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:
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:
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.
Next Steps
- Learn how to clone logins if you need to make a new login look like an existing one.
- If you’re not familiar with the tie between logins and database users, learn how this works.
- Understand why sp_change_users_login works and be ready to deal with orphaned users.
