Find orphaned users in SQL Server

set nocount on

go

DECLARE @databasename as varchar(200)

declare @strSQL as nvarchar (4000)

DECLARE Curse CURSOR local fast_forward

FOR

SELECT 

name 

FROM

master.dbo.sysdatabases 

WHERE

name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW')

OPEN Curse

FETCH next FROM Curse INTO @databasename

WHILE @@fetch_status = 0

BEGIN

SET @strSQL = 'USE '+ @databasename + ' 

declare @usrname varchar(100), @command varchar(100)

declare Crs insensitive cursor for

select name as UserName from sysusers

where issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

order by name

for read only

open Crs

fetch next from Crs into @usrname

while @@fetch_status=0

begin

IF exists(select * FROM master..syslogins WHERE [name] = @usrname)

select @command='' sp_change_users_login auto_fix, '' + @usrname

print @command

exec(@command)

fetch next from Crs into @usrname

end

close Crs

deallocate Crs'

EXEC dbo.sp_executesql @strSQL

fetch next from Curse into @databasename

end

close Curse

deallocate Curse