Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Script SQL Server Logins for Disaster Recovery


By:   |   Last Updated: 2015-06-18   |   Comments (8)   |   Related Tips: More > Scripts

Problem

In preparation for SQL Server disaster recovery, I need to make sure I can recover the logins onto another SQL Server instance. What do I need to know?

Solution

Microsoft has a knowledgebase article, KB918992 - How to transfer logins and passwords between instances of SQL Server, which provides two essential stored procedures that you'll likely need. I say likely because if you're only dealing with Windows user and groups, you won't need those two stored procedures. So, for instance, if you're in the situation where you are dealing with SQL Servers that only have Windows authentication turned on, you can extract the logins using this simple query:

-- If we know we just have Windows logins, we don't need anything special
-- Filter by type to only get users and groups
-- Filter by name to eliminate NT SERVICE\ and NT AUTHORITY
SELECT 'CREATE LOGIN [' + name + '] FROM WINDOWS;' 
FROM sys.server_principals 
WHERE type IN ('U', 'G')
  AND LEFT(name, 4) NOT IN ('NT A', 'NT S');

However, most folks have a combination of both, so let's talk about sp_helprevlogin.

Using sp_help_revlogin

Once you run the script in the KB article, you'll have two new stored procedures in your master database. One is used to translate the password hash into text form (sp_hexadecimal) and the other is what actually extracts the login with the appropriate information (sp_help_revlogin). The sp_help_revlogin is the stored procedure we will key in on. It produces the T-SQL code to recreate a login, even if it's a SQL Server login.

There are two ways to use sp_help_revlogin. The first way is without any parameters. This will produce a raw dump of every login. However, there's several we typically don't need. For instance:

  • The SQL Server based logins that start and end with two hash marks (##).
  • Any logins corresponding to NT AUTHORITY\Some Login
  • Any logins corresponding to NT SERVICE\Some Login

The second way is to use sp_help_revlogin with a specific login:

EXEC sp_help_revlogin 'Some Login'

Here's an example:

Using sp_help_revlogin to extract a single login

Filtering Down the Logins

If we run sp_help_revlogin without the parameter, we get the logins that are meaningless if we're moving the logins to a new server. Therefore, the trick is to call sp_help_revlogin for the specific logins we care about. Here's how to do that:

SET NOCOUNT ON;

DECLARE cursLogins CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.server_principals
WHERE 
  (LEFT(name, 4) NOT IN ('NT A', 'NT S')
    AND
   TYPE IN ('U', 'G'))
  OR
   (LEFT(name, 2) <> '##'
    AND
   TYPE = 'S');

DECLARE @Login sysname;

OPEN cursLogins;

FETCH FROM cursLogins INTO @Login;

WHILE (@@FETCH_STATUS = 0)
BEGIN
	EXEC sp_help_revlogin @Login;
	PRINT '';

	FETCH NEXT FROM cursLogins INTO @Login;
END

CLOSE cursLogins;
DEALLOCATE cursLogins;

This is a simple script where we're filtering with the SELECT query to exclude the types of logins we don't want. Obviously, we want all Windows logins except those that start with NT AUTHORITY or NT SERVICE. We can shorten selecting those out by just looking at the first four characters. And for SQL Server-based logins, we don't want anything that starts with ##. Once we have the list of logins we care about, we simply need to loop through and call sp_help_revlogin for each one.

Automating the Whole Thing

This is all well and good, but if you have a lot of servers, you don't want to have to run this manually each day against every server. If you take the script where we've filtered down the logins, that represents a good input file for SQLCMD. And if we can put it to the command-line, we can therefore use SQL Server Agent or any other automation engine (even Task Scheduler) to call SQLCMD and get an output file. For instance, here's what I'd use to call the query against a named instance (SQL2014) on a particular server (MySQLServer). The -E tells sqlcmd to connect as the Windows account running SQLCMD:

sqlcmd -E -S MySQLServer\sql2014 -i export_logins.sql -o sql2014_dr_logins.sql

Obviously, you could use network paths for the .sql files such as \\MyDRFileServer\DRScripts$\sql2014_dr_logins.sql. You would want to make sure that the output file gets somewhere where it can be retrieved in the event of a real disaster. As for that output file, it should look something like this:

Outfile from automating with sqlcmd

What About Matching Up SIDs?

If you understand how logins map into databases as users, you know the tie between the two is something called a SID, or Security IDentifier. If you were to just create a new SQL Server-based login (Windows logins aren't an issue because the SID comes from the Windows domain), a new SID would be generated. Obviously, if we're talking about a recovery situation, that won't work. We want the SID for the login to match the SID in the database. The good news is that sp_help_revlogin takes care of the SID as well because CREATE USER does have an option to specify the SID. This is how we keep things matched up. For instance, if I scroll over in that outfile from our SQLCMD command, we'll see that SID is being kept:

SID is handled by sp_help_revlogin

The first SID, for SA, is well known. it's always 0x01. However, the next SQL Server-based login we come to has a created SID. And we see that sp_help_revlogin does specify the parameter for CREATE LOGIN to ensure the SID will be maintained. Therefore, if you use sp_help_revlogin, you shouldn't have any issue with the SID matching up between the login and the user in the database. If for some reason you do, you can correct the situation by using sp_change_users_login.

Next Steps


Last Updated: 2015-06-18


next webcast button


next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, February 10, 2017 - 12:17:32 PM - Steven B Back To Top

  Thank you for this article, though when i run it on my server, not the store procedure but the cursor, it gives me "No login(s) found." instead of the login info. any help would be great, or explaination why this is showing up and not giving me "Create user..." info :(

 

 


Wednesday, August 05, 2015 - 10:53:07 AM - K. Brian Kelley Back To Top

Ramone,

 

  the purpose of this article is to extract the logins from one SQL Server automatically so that they be created on another SQL Server automatically as well. This is without revealing the password. Such is necessary when considering disaster recovery situations.


Wednesday, August 05, 2015 - 8:17:00 AM - Hussain Back To Top

Thanks a lot Brain.  


Wednesday, August 05, 2015 - 1:24:39 AM - Ramone Back To Top

I don't understand the principle of this article. If you want to create them with encrypted passwords you simply generate scripts on the DB or right click on the individual logins.


Wednesday, July 22, 2015 - 12:21:25 PM - Micah Back To Top

Very helpful article.  The part about filtering and automating was particularly useful.  Based on this article, we now have a scheduled job on each of our primary database servers that backs up the logins on a regular basis to a network location.  Should we ever need to fail over to one of our Secondary servers, we will be able to update the logins using that script and ensure all users will be able to connect.

Nicely done...


Friday, June 19, 2015 - 9:17:57 AM - K. Brian Kelley Back To Top

Hi Ray,

  the WHILE loop functions the same as a CURSOR. Both are row-by-row operations. :-)


Thursday, June 18, 2015 - 5:41:05 PM - Chris Back To Top

Excellent article! I like the way you are filtering out the unwanted logins and automating them at the same time using sp_help_revlogin.  Thank you for sharing. 


Thursday, June 18, 2015 - 5:32:06 PM - Ray Giacalone Back To Top

HI -  THX FOR POSTING THIS SOLUTION...I LIKE.  FOLLOWING IS JUST ANOTHER WAY TO FILTER ON THE LOGINS EXCEPT W/OUT THE CURSOR...IT USES A SIMPLE TABLE VARIABLE INSTEAD. 

----------------------------------------------------

USE MASTER;
GO

SET NOCOUNT ON;
DECLARE @T TABLE (ID INTEGER IDENTITY NOT NULL, LOGINS SYSNAME);
DECLARE @MAXID INTEGER, @LOGINS SYSNAME;

INSERT INTO @T (LOGINS)
 SELECT NAME [LOGINS]
  FROM sys.server_principals
  WHERE
   (LEFT(NAME, 4) NOT IN ('NT A','NT S')
   AND TYPE IN ('U','G'))
  OR
   (LEFT(NAME, 2) <> '##'
   AND TYPE = 'S');

SELECT @MAXID = MAX(ID) FROM @T;
SELECT @LOGINS = LOGINS FROM @T WHERE ID = @MAXID;

WHILE @MAXID > 0
BEGIN
 
 --DO THE WORK HERE
 EXECUTE dbo.sp_help_revlogin @LOGINS;
 PRINT '';

 --GET THE NEXT VALUE HERE
 SET @MAXID -=1;
 SELECT @LOGINS = LOGINS FROM @T WHERE ID = @MAXID;
 
END

 


Learn more about SQL Server tools