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

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

!