Fix Logins -> DB Users

When a database is restored from one server to another, the databases users are still mapped to the guid of the logins on the original server. To remap the database users to the logins on the gaining server, you can drop and recreate the user or run sp_change_users_login.  The script below will run this for all users.


USE [db_name]
GO


SET NOCOUNT ON
GO

declare @tab TABLE ( id int identity(1,1), sqlcmd nvarchar(4000) )
declare @id int, @sqlcmd nvarchar(4000)

insert into @tab ( sqlcmd )
select 'exec sp_change_users_login ''update_one'', ''' + A.name + ''',''' + B.name + ''''
from sys.sysusers A
inner join sys.syslogins B
on lower(A.name) collate SQL_Latin1_General_CP1_CI_AS = lower(B.name) collate SQL_Latin1_General_CP1_CI_AS
where issqluser = 1

select @id = max(id) from @tab

while @id > 0
begin
   select @sqlcmd = sqlcmd from @tab where id = @id
   print @sqlcmd
   exec sp_executesql @sqlcmd
   set @id = @id - 1
end

GO