How do I... Transfer logins from one SQL Server 2005 instance to another?

In my last How do I..., I discussed how to restore a SQL Server to a new server. After the article published, I had some questions on the best way to move SQL Server logins from one SQL Server 2005 instance to another SQL Server 2005 instance.

I want to preface that you can move logins from one SQL Server to another SQL Server in many different ways. In my experience, I have found that this is the easiest way to move logins from one SQL Server to another SQL Server while keep existing passwords.

We will begin by downloading a script called Sp_Help_RevLogin.

Note: If you are transferring logins from SQL Server 2000 to SQL Server 2005, download this script. Next, navigate to Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. Simply highlight and copy the script from the link provided and paste into SQL Server Management Studio, as shown in Figure A. Note: When you first open SQL Server Management Studio, you must click New Query and select the Master database prior to pasting the script and clicking Execute.

This blog post is also available as a TechRepublic gallery and TechRepublic download.

Figure A

After clicking Execute, you will receive a success message, as shown in Figure B.

Figure B

For the purpose of this tutorial, I have created roughly 20 logins in SQL Server, as shown in Figure C. They all have unique passwords. In your environment, you may have hundreds of logins or thousands, but it will not matter. The script is lightning fast. Scripting out 100 logins or 1,000 logins takes the same amount of time.

Figure C

Our next step is to select New Query in SQL Server Management Studio and type the following query, as shown in Listing A.

Listing A

Use Master
Go
Exec sp_help_revlogin

Figure D provides you with an example of the above syntax.

Figure D

Your next step is to copy the output from the script and save it as a .sql file. Let's save it as logins.sql. Simply right-click on any area in the output window and choose Save Results As... from the menu (Figure E).

Figure E

Next, type a filename, such as logins.sql, and select All files (*.*) as Save As Type (Figure F). A logins.sql script is saved to your preferred location on the local disk or network, as shown in Figure G.

Figure F

Figure G

After you restore the databases associated with the scripted out logins to a new SQL Server, you are ready to re-create the permissions associated with the restored databases. Open SQL Server Management Studio and select File | Open | File, as shown in Figure H.

Figure H

Next, browse to the network or shared folder where you saved your logins.sql script. Select the script and choose Open (Figure I).

Figure I

You must now provide the appropriate credentials in the Connect To Database Engine, as shown in Figure J. Note: Make sure you are pointing to the Master Database and execute the script as shown in Figure K.

Figure J

Figure K

If there are logins that currently exist in the SQL Server, you will get the following Messages in red (Figure L). You can ignore them, as they are logins that already exist. You can also edit the script to pull out all Windows logins, which is what I should have done.

Figure L

We have now successfully recreated the logins. Figure M and Figure N provide you screenshots of the before and after Login.sql. We are almost finished.

Figure M

Figure N

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 in Listing B. Figure O displays how this 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

Figure O

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.