Transfer SQL logins for users with a large number of SQL-authenticated logins

SQL Server logins are the credentials that enable users to connect to the Database Engine instance. SQL logins are distinguished based on the type of authentication method: Windows-authenticated, SQL Server–authenticated, Certificate, and Asymmetric key. One SQL login can be mapped to only one user in each database.

Database users represent the identity of a SQL Server login when connected to a database. A database user maps to an existing SQL Server login except in the case of contained databases where users are able to connect to a database without authenticating a login at the Database Engine level. Because of the isolation from the Database Engine contained databases can be easily moved to another SQL Server instance.

In a non-contained database, when performing a SQL migration from one instance of SQL Server to another, SQL logins also must be migrated to prevent database users configured for the database to become orphaned. An orphaned user is a user without a corresponding SQL login.

A SQL login is required for access to the SQL Server. A SQL login without a corresponding user in a specific database cannot connect to a database, although it can connect to a server and an orphaned user cannot connect to a server.

Information about the mapping of a SQL Server login to a database user includes the name of the database user and the Security ID (SID) of the corresponding SQL login:

Illustration showing the mapping of a SQL Server login to a database user

The Import and Export Wizard cannot be used to copy logins from one SQL Server instance to another. Also, backing up and restoring a database does not copy login data because the SQL logins are stored outside the database.

Transfer SQL logins by using the Copy Database Wizard

To use the Copy Database Wizard for migrating SQL logins:

  1. Connect to the source and destination SQL Server instances in SQL Server Management Studio.
  2. Start the SQL Server Agent on the destination SQL Server instance.
  3. Right-click the database on the source SQL Server instance from which you want to transfer SQL logins, click Tasks, and select the Copy Database command to start the Copy Database Wizard.
  4. On the Source Server page, specify the source SQL Server instance and click the Next button.
  5. On the Select A Destination Server page, specify the destination SQL Server instance and click the Next button.
  6. On the Select Server Objects page, where you choose which objects to copy, logins are selected by default:

    Transfer SQL logins by using the Copy Database Wizard

Note that the Select Server Objects page is available in the wizard only if the source and destination are different servers. This method will not copy just the logins it will also copy the entire database.

Transfer SQL logins by using the Script Login As method

Using the Script as function in SQL Server Management Studio is a simple way to re-create Windows authenticated SQL logins.

To create a script to transfer a SQL login on a new instance:

  1. In the Object Explorer pane expand the Security->Logins node.
  2. Right-click the SQL login you wish to migrate, and choose the Script Login As-> CREATE To->New Query Editor Window command:

    Transfer SQL logins by using the Script Login As method

This creates a script in a new query window that can be run on the destination SQL Server instance. In case of SQL-authenticated SQL logins these logins are in disabled state and with a random password:

USE [master]
GO

/* For security reasons the login is created disabled and with a random password. */
/****** Object:  Login [dnnuatuser]    Script Date: 2/28/2015 2:40:23 AM ******/
CREATE LOGIN [dnnuatuser] WITH PASSWORD=N'î覫ù±_K¨_þèDÙ=^R|''î_ƒK¤_¶qù[(', DEFAULT_DATABASE=[AdventureWorks2014], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

ALTER LOGIN [dnnuatuser] DISABLE
GO

In this case it will be necessary to reset passwords associated with SQL logins before the user associated with the login can connect. Also a login needs to be enabled because even after a password resetting principal assigned to a login will not be able to login to a destination instance:

Error message - login to a destination instance has failed

In case of users with a large number of SQL-authenticated logins every login need to be scripted, passwords reset, and enabled manually.

Transfer SQL logins by using the script published in Microsoft Knowledge Base article KB918992

Microsoft provides a script that will create two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin. To export SQL logins from a SQL Server instance first execute the sp_help_revlogin stored procedure:

EXEC sp_help_revlogin

The output of this procedure is a script that should be executed on the destination instance to re-create logins exported from the source instance. This script also migrates the passwords associated with the logins by using the encrypted password.

If a source SQL Server instance is case-insensitive, and a destination SQL Server instance case-sensitive users must type the passwords in all uppercase letters after transferring the SQL logins and the passwords to the instance on a destination server. In the reverse case users cannot log in by using the logins and the passwords transferred to the instance on a destination server unless the original passwords do not contain letters or if all letters in the original passwords are uppercase.

If the SQL logins are transferred to a destination instance of SQL Server in a different domain the script needs to be edited. Note that the integrated logins granted access in the new domain will not have the same SID as the logins in the source domain, and the database users for these logins will be orphaned. Also, a login that already exists in the destination SQL Server instance may have the same SID as a SID in the output script. In this case, when the output script in executed on the destination instance it will report the following message:

Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.

The output script of the sp_help_revlogin procedure needs to be carefully reviewed before it’s executed on a destination instance.

Transfer SQL logins by using ApexSQL Script

ApexSQL Script is a SQL Server database migration tool that scripts database objects and data into a single or multiple SQL scripts, .NET solutions, or executable installers. Using ApexSQL Script you can make a SQL script to migrate selected database users with all assigned SQL logins.

To transfer SQL logins using ApexSQL Script start a new project dialog, connect to a database and under the Structure script tab select the Script logins with users option and click the Open button:

Transfer SQL logins using ApexSQL Script

In the Results grid select users for migration and click the Script button:

Click the Script wizard button in ApexSQL Script

Follow the Script wizard steps until the script is created. The script creates both the user and the corresponding login:

The SQL script creates both the user and the corresponding login

Executing this SQL script on a different domain doesn’t require changes in the script because ApexSQL Script allows choosing only users and logins. However, you’ll need to reset passwords for SQL Server–authenticated logins manually. Users are in an enabled state and can be logged on a destination instance:

Manual password reset is needed for SQL Server–authenticated logins

Useful resources:
Copy Database Wizard (Select Database Objects)
How to transfer logins and passwords between instances of SQL Server
Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)
Basic SQL Server Security concepts – logins, users, and principals

April 28, 2014