Change login and user mapping in SQL Server

Question

I have a login1 and two users user1 and user2. All three are created previously. Originally, login1 was mapped with user1 and now I want to change to map with user2.

First, I tried

ALTER USER user2 WITH LOGIN = login1;

But SSMS shows

Cannot remap user to login 'login1', because the login is already mapped to a user in the database.

Then I tried

EXEC sp_change_users_login 'Update_One','user2','login1'

It shows

The login already has an account under a different user name.

When I search with these error messages, most of the posts are talking about why this would happen or how to identify the related (origin) user?

But my focus is as titled, how can I change from one existing user to another for an existing login? Is it possible to achieve without re-creating any of them?

Answer 1

how can I change from one existing user to another for an existing login

I intend to leave user1 orphaned

Create a new login and map user1 to the new login before mapping user2 to login1.

eg

create login login1 with password = '09)(*)(4u239u4s'
create login login2 with password = '09)(*)(4u239u4s'
create user user1 for login login1
create user user2 for login login2
go
create login newlogin with password='2348(*-09udjA'
alter user user1 with login=newlogin
alter user user2 with login=login1
drop login newlogin --user1 is now orphaned

Answer 2

You try to change the user to the desired login. This is fine.

But that login already have a user in a database. A login cannot be "mapped" to two users in a database.

One option is to not remap the login.

The other option is to remove that user to which your login is currently mapped to.

• Circle Hsiao: when you said "remove that user", you mean DROP USER user1?
• Circle Hsiao: Is there a way not to drop and recreate it?
• Tibor Karaszi: You have to decide which if the two usrs you want mapped to that login. You can't have two mapped to the same login. Think about it, and it makes sense. I can't really say more without knowing more about your situarion.
• Circle Hsiao: I intend to leave user1 orphaned
• Tibor Karaszi: There's no option to unmap a user, I'm afraid. That would be something like WITH NO_LOGIN in the ALTER USER command. But the documentation doesn't have anything like that. You can suggest this to Ms, but be sure to include your scenario. As for now, take a step back and see if you can avoid this requirement in the first place.