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
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
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.