Data Management optimize

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

<img src="http://t.cbsimg.net/i/z/200606/how_110x85.jpg" align="right" border="0" height="85" hspace="5" vspace="5" width="110" />In my last How do I..., I discussed <a href="http://www.techrepublic.com/blog/window-on-windows/?p=454" target="_blank">how to restore a SQL Server to a new server</a>. 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.<br /> 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.

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.

15 comments
Jason Jeffers
Jason Jeffers

I have a nice script that I found handy, you can use it to create logins from database users, this script is using a stored procedure which you can find at the following URL: http://www.sqlserveroptimizer.com/2011/08/how-to-script-logins-from-user-database-in-sql-server-20052008-r2/ Good luck USE MyDatabaseName DECLARE @login nvarchar(50) DECLARE logins_cursor CURSOR FOR SELECT l.name FROM sys.database_principals u INNER JOIN sys.server_principals l ON u.sid=l.sid OPEN logins_cursor FETCH NEXT FROM logins_cursor INTO @login WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_help_revlogin @login FETCH NEXT FROM logins_cursor INTO @login END CLOSE logins_cursor DEALLOCATE logins_cursor GO

cw81
cw81

Great article but I am getting the following error message when I try to run the login.sql on the new server with the restored database: Supplied parameter sid is in use.

mhickma
mhickma

The free tool from Idera is not as useful as this article. The free tool doesn't pass the password, and the login is disabled "login is created disabled and with a random password". This is not a problem when using this article, however, mapping and privileges to the database\s in the script didn't seem to work well when following this article. For me it was important to pass the passwords, as some DBA before me failed to catalog the passwords for the SQL Logins. NT users were a breeze, other than having to go through each one and map them with proper privileges.

jnsnfl
jnsnfl

In SQLSrvr 2005 Express Edition I get the following errors: Before modifying your script I got a message stating the "srvid" is an invalid column. So I commented it out to get the sp_'s to run. Then I got a message stating there was an implicit conversion error at line 80 causing the following: Msg 16922, Level 16, State 1, Procedure sp_help_revlogin, Line 80 Cursor Fetch: Implicit conversion from data type nvarchar to varbinary is not allowed. I have four SQLServer login's in need to recover and these are the only ones I can't get because of this error. Help?

felonggj
felonggj

We use domain accounts as logins for SQL 2005 will the script work with these types of accounts?

tgoatley
tgoatley

Does the same apply to MSSQL Express??

jd
jd

Great article -- there is also a free tool that will speed up this job and will move logons AND permissions (which can often be the more challenging task.) SQLpermissions creates a T-SQL script that you can use to automatically move logons and permissions from one server to another. You can download it free at www.idera.com/freetools.

gary.begin
gary.begin

Good article, one additional option for resolving orphaned logins is the sp_change_users_login procedure. Running sp_change_users_login 'report' will show mismatched logins. Then, running sp_change_users_login 'update_one' will allow you to synchronize the SIDs of the mismatched logins to existing users.

shankardudhane
shankardudhane

i have a liscence 2007 sql server do this procedure will work on it?

jnsnfl
jnsnfl

Due to missing "srvid" from some instances of SQL Server 2005 (incl. Express) AND SQL Server 2008, I ran into trouble, see my original post. After a little bit of review and modifcation I changed the cursor creation body to 1) ignore the srvid value since it doesn exist and 2) to explicitly convert the nvarchar to varbinary. The solution worked great for me otherwise! Kudos to the script master creating the oroginal... IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, status, convert( varbinary, password) FROM master..syslogins WHERE name 'sa' -- AND srvid IS NULL ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, status, convert( varbinary, password) FROM master..syslogins WHERE name = @login_name -- AND srvid IS NULL

Steven S. Warren
Steven S. Warren

Listing B. has the sp_change_users... inside it. I made it easier to run. If you have thousands of logins, running just the update one will take forever. Check it out.

Steven S. Warren
Steven S. Warren

We have sql server 6.5, 7.0, 2000 and 2005. We have SQL 2008 in beta. There is no sql server 2007. Can you explain your question further to me? Best, Steven These instructions will work for SQL Server 2000 and 2005. I believe it will work on SQL Server 7.0 but I have not tested it.

chumphre
chumphre

Steven, I was running Listing B when it seemed to stop updating so I canceled the execution. When I tried to execute it again I get the following: CAI11mNT Synchronization of Logins in Progress Msg 15289, Level 16, State 1, Procedure sp_change_users_login, Line 75 Terminating this procedure. Cannot have an open transaction when this is run. CAI12mNT Synchronization of Logins in Progress Msg 15289, Level 16, State 1, Procedure sp_change_users_login, Line 75 Terminating this procedure. Cannot have an open transaction when this is run. . . . Any ideas on how to resolve this? I am able to run sp_change_users_login 'Report' and I can run sp_change_users_login 'Update_one', 'CAI11mNT', 'CAI11mNT' without a problem. I am new to SQL Server! Thanks