SQL Server - Orphaned users and how to Synchronise Users and Logins after SQL Server 2005 restore

22 August,2008 by Jack Vamvas

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 sqlservercentral.com) . 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)

BEGIN

PRINT @UserName + ' Synchronization of Logins in Progress'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

END

CLOSE Cursor_OrphanedUser

DEALLOCATE Cursor_OrphanedUser

Go
------------------------------------------CODE END--------------------------------------------------

 Related Posts

SQL Server – Deleting Distributed Orphaned Transactions

SQL Server - RESTORE VERIFYONLY


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on SQL Server - Orphaned users and how to Synchronise Users and Logins after SQL Server 2005 restore


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer