Remap Users
A cursor script that regenerates and executes ALTER USER WITH LOGIN statements for every mapped user in a database.
The bulk companion to the single-user orphan fix. This script walks sysusers in the current database, finds every principal that has a login mapping (islogin = 1), and generates plus executes an ALTER USER ... WITH LOGIN statement for each one - re-establishing the user-to-login link after a restore or SID mismatch. It skips dbo, guest, INFORMATION_SCHEMA and sys.
DECLARE @tSQL nVarChar(256)
DECLARE UserMappings CURSOR FOR
SELECT
'ALTER USER [' + name + '] WITH LOGIN = [' + name + ']'
FROM
sysusers
WHERE
islogin = 1
and name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
OPEN UserMappings
FETCH FROM UserMappings INTO @tSQL
WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
---------------------------------------------------------------------------------------
PRINT @tSQL
EXEC(@tSQL)
---------------------------------------------------------------------------------------
FETCH FROM UserMappings INTO @tSQL
END--WHILE
CLOSE UserMappings DEALLOCATE UserMappings