Correcting Orphaned Users

When migrating databases between servers, userids will become invalidated, and users attempting to access the newly restored database will receive login failures when attempting to connect.  To correct this problem, DBAs should fix all orphaned logins after the database has been restored.  There are many examples of this script, but this is my version:

-- Identify all Orphan Users and correct their logins.
-- List orphan users
EXEC sp_change_users_login 'Report'

-- For each orphan, link the user in the current db to an existing SQL login
DECLARE @CurrentUserName varchar(25)
DECLARE OrphanedUsers CURSOR FOR
   SELECT CurrentUserName = name 
     FROM sysusers
    WHERE issqluser = 1 
      AND (sid IS NOT NULL AND sid <> 0x0)
      AND suser_sname(sid) IS NULL
    ORDER BY name

OPEN OrphanedUsers
FETCH NEXT FROM OrphanedUsers
INTO @CurrentUserName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @CurrentUserName, @CurrentUserName
FETCH NEXT FROM OrphanedUsers
INTO @CurrentUserName
END
CLOSE OrphanedUsers
DEALLOCATE OrphanedUsers

Leave a Reply

Your email address will not be published. Required fields are marked *