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.

SET NOCOUNT ON
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 
SELECT a.name 
FROM sysusers a
inner join master..syslogins b on a.name =b.name
WHERE       a.issqluser = 1
		AND a.sid is not null
		AND a.sid <> 0X01
		AND suser_sname(a.sid) is null
ORDER BY a.name

IF (select count(UserName) from @OrphanUserTable)=0
PRINT 'No orphan users found'
 
SET @rowcount = 1
WHILE @rowcount < = (select count(UserName) from @OrphanUserTable)
BEGIN
 
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

END


 

Leave a Reply

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