Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to Fix Orphaned users and Synchronise Users and Logins after SQL Server restore

22 August,2008 by Tom Collins

SQL database users can become SQL orphaned users and lose the mapping with the associated SQL Server Login , particuarly  after a SQL Server restore. This may require the DBA to synchronise Users and Logins.

I'm  working on a series of moving SQL Server 2005 servers  around , as part of a large Data Centre migration project. Part of the process requires backing up and restoring from ServerA to ServerB . In the situations the ids do not match the database user loses he connect with the SQL Server Login.

A script I've found useful (courtesy of . This  synchronises the Database Users with the SQL Server Logins

-----------------------------------------CODE START--------------------------------------------

DECLARE @UserName nvarchar(255)

DECLARE Cursor_OrphanedUser cursor for

SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name 

OPEN Cursor_OrphanedUser

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

WHILE (@@fetch_status = 0)


PRINT @UserName + ' Synchronization of Logins in Progress'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName


CLOSE Cursor_OrphanedUser

DEALLOCATE Cursor_OrphanedUser

------------------------------------------CODE END--------------------------------------------------

 Related Posts

SQL Server – Deleting Distributed Orphaned Transactions



Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to Fix Orphaned users and Synchronise Users and Logins after SQL Server restore | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer