Resynchronize Orphaned Users

This issue happens often when restoring databases from one server to another.

Let's say we have a database A on Server X.

If database A has a user user1. It most likely maps to a SQL login user1, with its own SID (unique identifier).

If we restore database A on server Y, it will attempt to map the user1 to a SQL login on server Y, by using the SID.  Since the SQL login user1 exists on server Y, but with a different SID, the mapping doesn't happen, hence the user on the database gets orphaned.

We fix that by running


EXEC sp_change_users_login 'Update_one', @UserName, @UserName


However, below we have a cursor that identifies orphaned users and synchronized them.

USE [database name goes here]

--Variable to hold current UserName to process
DECLARE @UserName nvarchar(255)
DECLARE @rowcount int
--Create Table to hold the orphan Users

DECLARE @OrphanUserTable table
(rownum int identity(1,1), UserName sysname)

insert into @OrphanUserTable 
FROM sysusers a
inner join master..syslogins b on
WHERE       a.issqluser = 1
		AND a.sid is not null
		AND a.sid <> 0X01
		AND suser_sname(a.sid) is null

IF (select count(UserName) from @OrphanUserTable)=0
PRINT 'No orphan users found'
SET @rowcount = 1
WHILE @rowcount < = (select count(UserName) from @OrphanUserTable)
PRINT @UserName + ' user name being resynced'
--Re-sync the currently selected orphan user
EXEC sp_change_users_login 'Update_one', @UserName, @UserName

--Get Next Orphan User
SET @rowcount = @rowcount + 1



Leave a Reply

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