When restoring a database from one server to another we often have to delete a user from a db then add it back. This task can be performed more easily using a stored procedure built into SQL Server.
sp_change_users_login 'update_one','user','login'‘update_one’ should stay as is, this tells it to update the specified user
‘user’ is the username in the current data base you wish to fix
‘login’ is the Security login in the database that you wish to associate the user with.
This is supported in SQL 2000 and higher, although the documentation suggests it may be removed in the future.
More reading:
http://msdn.microsoft.com/en-us/library/ms174378.aspx
Example script use to print TSQL to update database user with new login:
declare @user as nvarchar(50)
declare @sql as nvarchar(100)
set @user = (select top 1 mp.name
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
where mp.name<>'dbo')
print 'Use ['+DB_NAME()+']'
print 'go'
set @sql='sp_change_users_login ''update_one'','''+@user+''','''+@user+''''
print @sql
CloudSkills-PH 