[Logins] Resync logins after moving a database across servers

Not mine. This is courtesy of Sql-server-performance.com, which you should already be reading. Putting it here for my edification.

When you move a database from one server to another, the user IDs will no longer match. So you can't create a user with the same name in that database, and you can't fix the problem easily. This will sync it.


--Script to resync orphan SQL Server login IDs and database user IDs


USE database_name --Change to active database name
GO

DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name

OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur