Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SP_change_users_login and SQL Server 2008 onwards

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.

Comments

Posted by Jason Brimhall on 30 March 2010

Thanks Gethyn.

Posted by Dukagjin Maloku on 31 March 2010

Thanks for the info!

Posted by Ricky Lively on 31 March 2010

set nocount on

declare @sql varchar(2000),

       @dbname sysname,

@username varchar(128),

       @RowCount1 int

CREATE TABLE ##list

(

           [id] int identity(1,1),

           [Database Name] sysname COLLATE Latin1_General_CI_AS,

           [Orphaned User] sysname COLLATE Latin1_General_CI_AS

)

Set @dbname = db_name()

Set @sql = 'SELECT ''' + @dbname + ''' AS [Database Name],

   CAST(su.[name] AS sysname) COLLATE Latin1_General_CI_AS  AS [Orphaned User]

   FROM [' + @dbname + ']..[sysusers] su

   WHERE su.[islogin] = 1

   AND su.[name] <> ''guest''

   AND NOT EXISTS

   (

SELECT 1

FROM [master]..[syslogins] sl

WHERE su.[sid] = sl.[sid]

   )

   AND EXISTS

   (

SELECT 1

FROM [master]..[syslogins] s2

WHERE su.[name] = s2.[name]

   )

   ORDER BY su.[name] DESC;'

2

Insert ##list Exec (@sql)

Set @RowCount1 = @@rowcount

If @RowCount1 > 0

Begin

print '-- Scripted -- ' + @@SERVERNAME + ' -- ' + CONVERT(VARCHAR, GETDATE(), 121)

print 'USE ['  + @dbname + '];'

print 'GO'

End

While @RowCount1 > 0

Begin

SELECT @username = [Orphaned User] FROM ##list WHERE [id] = @RowCount1;

Set @sql = 'sp_change_users_login ''auto_fix'', ''' + REPLACE(@username,'''','''''''''') + ''';' + char(10) + 'go'

print @sql

--Exec @sql

Set @RowCount1 = @RowCount1 - 1

End

drop table ##list

Posted by Anonymous on 4 April 2010

Pingback from  Desktop Messenger 2.0 Review | Host Rage

Leave a Comment

Please register or log in to leave a comment.