http://www.sqlservercentral.com/blogs/sqldbauk/2010/03/30/sp_5F00_change_5F00_users_5F00_login-and-sql-server-2008-onwards/

Printed 2014/09/21 12:09AM

SP_change_users_login and SQL Server 2008 onwards

By Gethyn Ellis, 2010/03/30

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.