Subscribe to this blog
Briefcase
Print
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.
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