Script SQL Server Logins for Disaster Recovery

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:

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:

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:

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:

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.

About the author

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.

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 :(

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.

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.

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.