Clean Up Orphaned SQL Users
2013-04-14 12:23
This used to be done with sp_change_users_login, but that procedure is being removed in favor of alter user.
--http://ask.sqlservercentral.com/questions/3173/identifying-orphaned-users.html
--http://stackoverflow.com/a/2874493
--Modified by me to use correct search for orphans
DECLARE @Username VARCHAR(100),
@cmd VARCHAR(100)
DECLARE userlogin_cursor CURSOR FAST_FORWARD FOR
SELECT UserName = dp.name
--, UserSID = dp.sid
FROM sys.database_principals dp
WHERE dp.type = 'S'
AND (dp.sid is not null AND dp.sid <> 0x0)
AND (LEN(dp.sid) <= 16)
AND SUSER_SNAME(dp.sid) is null
ORDER BY dp.name
FOR READ ONLY
OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']'
EXECUTE(@cmd)
FETCH NEXT FROM userlogin_cursor INTO @Username
END
CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor