THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Scripting SQL Server Logins

You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked.

Understanding Login Components

To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer.

SP_HELP_REVLOGIN

SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here http://support.microsoft.com/kb/918992/. Once you have create the procedures you can easily generate the create statements that allow you to copy your logins.

As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:

sp_help_revlogin 'SQLScript'

RESULTS:

/* sp_help_revlogin script

** Generated Oct 30 2007 9:23AM on laptop1 */

-- Login: SQLScript

CREATE LOGIN [SQLScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.

Published Tuesday, September 30, 2008 2:58 PM by ejohnson2010

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nathan Bunting said:

Very useful, thanks :)

September 14, 2010 7:59 AM
 

David said:

Hi there,

I have tried to script the logins in my server but it seems like its not working out for me. What might be the problem for my case. Am running sql 2005.

This is the script i get when i execute the SP_HELP_REVLOGIN procedure:

/****** begin of script ******/

USE [master]

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_help_revlogin]

SELECT 'Return Value' = @return_value

GO

/****** end of script ******/

Any help please. Where am i going wrong yet i copied the script at:

http://support.microsoft.com/kb/918992

I will highly appreciate any help

Regards,

David.

June 17, 2011 2:44 AM
 

krishna said:

Do you see any errors?

July 13, 2011 4:55 PM
 

Ashwin said:

Here is a script that I found useful, this script calls a stored precedure sp_help_revlogin for each login that is being used by the database:

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

November 30, 2011 10:44 AM
 

Soledad said:

Thanks, you saved my life

March 1, 2012 12:44 PM
 

vinay said:

Thanks much, I was trying to automate sql logins only for one database with out using SP_HELP_REVLOGIN and was struggling at PW as i was missing out HASHED. you example really helped me to get that thing noticed and solved my issue.

Cheers

February 24, 2014 1:33 AM
 

Gary said:

Thank you!  I've done this before, but could not remember the details.  Your post was very helpful.  

April 2, 2014 10:45 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement