Today I discovered, from a post on the forums and a follow up dig into BOL that sp_change_users_login is to be depreciated in future versions of SQL Server and the new ALTER USER should be used instead.  Now let me make it clear here, sp_change_users_login is still available in SQL Server 2008, but BOL suggests that  it will be removed in future versions.

Curious, I looked up ALTER USER in BOL and I find that it does most of what sp_change_users_login  does in terms of fixing orphaned users…with a small exception  it doesn’t have the equivalent of the

sp_change_users_login 'report'

to give us a report of any orphaned users in the database.

This prompted me to dig into the code of the sp_change_users_login to see what the report returns… it runs a select from on sysusers. According to BOL  the sysusers system table is included as a view for backward compatibility and that this will be removed in future versions. I checked the BOL entry that matches old SQL 2000 system tables to SQL Server 2005 system views and sysusers becomes sys.database_principals in SQL Server 2005 and onward . Here’s my attempt at finding orphaned users, i have tried this in two tests and it returns the same results as sp_change_users_login:

select dp.name, sid, *
from sys.database_principals dp
where
dp.sid not in (select sid from sys.server_principals) 
and type = 'S' -- SQL Server User
and sid is not null -- DB principal has a Sid
and sid <> 0x00 -- The sid is not this

So run the above select to pull out orphaned users…I have one with a user name of SCTest in a database just restored from a backup of production into  test… I know I have login called SCTEST on the test instance so how do match them up using the new syntax :

ALTER USER SCTest with LOGIN = SCTest

This will  match the database user with server login.

I know best practice dictates to always use windows authentication where possible…but in case you do have any legacy applications or non-windows users hanging around you may find this useful.