Pages

Sunday, April 14, 2013

Clean Up Orphaned SQL Users

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