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