This is what sys.sp_change_users_login does

When you copy a database from one server to another one of the common tasks is to run sys.sp_change_users_login 'Report' and verify that there are no unexpected orphaned users; which occurs when a sys.database_principal (user) doesn't have a matching sys.server_principal (login).

But I noticed a few days ago that I had never really looked into exactly how it works, especially as it's possible to link up a user to a completely different login. So I designed a test.

Create a login and user. Then remove the login and recreate it. This leaves the orphaned user.

Use master

Create Login [TestLogin] With Password = 'Akajlksdfjl12#!@#'
Create User [TestLogin]

Drop Login [TestLogin]
Create Login [TestLogin] With Password = 'Akajlksdfjl12#!@#'

Exec	sys.sp_change_users_login 'Report'

sys.sp_change_users_login tells you the SID which is a good indication that it's going to do something with it. So I confirm the user and login SIDs, run the standard fix, and check them again afterwards.

Select	sid
From	sys.database_principals
Where	name = 'TestLogin' 
-- Result: 0x4C6D6889223053498C11B9F6DD347DD5
Select	sid
From	sys.server_principals
Where	name = 'TestLogin' 
-- Result: 0x38EF7EAC1C7D4749859A902F16FC3CC2

Exec	sys.sp_change_users_login 'AUTO_FIX', 'TestLogin'

Select	sid
From	sys.database_principals
Where	name = 'TestLogin' 
-- Result: 0x38EF7EAC1C7D4749859A902F16FC3CC2
Select	sid
From	sys.server_principals
Where	name = 'TestLogin' 
-- Result: 0x38EF7EAC1C7D4749859A902F16FC3CC2

What it has done is change the user SID to match the login SID. This makes a lot of sense and means that the SID values ultimately map the two together; it wouldn't be possible to change the login SID as this would mean that all the user SIDs used in other databases would then be wrong.

I should also mention if you really want to know how it works the source code is visible. I didn't want to read through it because I learn better from experience. There is some really funky stuff in there though…

Select	Object_Definition(Object_Id('sys.sp_change_users_login')) As Definition

/*
...
EXEC %%System().AuditEvent(ID = 1196184405, Success = 0, TargetLoginName = @LoginName, TargetUserName = @UserNamePattern, Role = NULL, Object = NULL, Provider = NULL, Server = NULL)
...
*/			

WTF.