Fix orphan database users in a database after restoration

-- Fix orphan database users in a database after restoration

-- SQL 2005 and above

-- Assumption: Logins are created in the server for orphaned database users

declare @slno int, @maxslno int,@username varchar(100)

set @slno = 0
select identity(int,1,1) as slno ,
a.name as username into #temp
from sys.sysusers a,
sys.syslogins b
where not ( a.issqlrole = 1 or a.isntgroup = 1 or a.isntuser = 1 )
and a.hasdbaccess =1
and
not ( b.isntgroup = 1 or b.isntuser = 1 )
and a.name = b.name


select @maxslno = max(slno) from #temp

while @slno < @maxslno
begin
      set @slno = @slno + 1
      select @username = a.username from #temp a where slno = @slno

       EXEC sp_change_users_login 'auto_fix',@username

end

drop table #temp