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.