Disaster recovery planning is an essential part of managing a SQL Server environment. One important aspect of this planning is ensuring that you can recover the logins onto another SQL Server instance. In this article, we will discuss the steps to transfer logins and passwords between instances of SQL Server.

Using a Simple Query

If you are dealing with SQL Servers that only have Windows authentication turned on, you can extract the logins using a 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');

This query will generate the T-SQL code to recreate the logins on another SQL Server instance.

Using sp_help_revlogin

In most cases, SQL Servers have a combination of both Windows and SQL Server-based logins. To transfer these logins, we can use the stored procedure sp_help_revlogin provided by Microsoft. This stored procedure is part of the knowledgebase article KB918992 – How to transfer logins and passwords between instances of SQL Server.

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 stored procedure 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, which will produce a raw dump of every login. However, there are several logins that we typically don’t need, such as SQL Server-based logins that start and end with two hash marks (##) and logins corresponding to NT AUTHORITY\ or NT SERVICE\.

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

EXEC sp_help_revlogin 'Some Login';

This will generate the T-SQL code to recreate the specified login.

Filtering Down the Logins

If we want to filter down the logins to only include the ones we care about, we can use the following script:

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 script filters the logins based on specific criteria and calls sp_help_revlogin for each login. This allows us to generate the T-SQL code for the logins we care about.

Automating the Process

If you have a lot of servers and don’t want to run the script manually against each server, you can automate the process using SQL Server Agent or any other automation engine. You can use SQLCMD to execute the script and generate an output file. For example:

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

This command uses SQLCMD to execute the script export_logins.sql against a named instance (SQL2014) on a particular server (MySQLServer). The output file sql2014_dr_logins.sql will contain the T-SQL code to recreate the logins.

Matching Up SIDs

When transferring logins, it’s important to ensure that the Security IDentifier (SID) of the login matches the SID in the database. The sp_help_revlogin stored procedure takes care of this by specifying the SID parameter in the CREATE LOGIN statement. This ensures that the SID will be maintained.

In conclusion, transferring logins and passwords between instances of SQL Server is a crucial step in disaster recovery planning. By using the provided scripts and stored procedures, you can easily transfer logins and ensure that the SIDs are matched up correctly.