How do I... Transfer logins from one SQL Server 2005 instance to another SQL Server 2005 instance?
Move SQL Server logins
We still have the final task of resolving permissions. When you move a database from one server to another server that is running SQL Server, a mismatch happens between the SIDs (security identifier numbers). The sp_change_users_login can fix the issue.
Open SQL Server Management studio and run the script as shown. The accompanying image displays how Listing B will look in SQL Server Management Studio.
Listing B
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
Run this script on each database for which you have to synchronize logins. Once you have finished running this script on each database, you have successfully moved SQL Server logins from one server to another.
Open SQL Server Management studio and run the script as shown. The accompanying image displays how Listing B will look in SQL Server Management Studio.
Listing B
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
Run this script on each database for which you have to synchronize logins. Once you have finished running this script on each database, you have successfully moved SQL Server logins from one server to another.
