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

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 SQL logins need to be transferred, select Tasks, and click the Copy Database command to start the Copy Database Wizard:

   

4. The Welcome to the Copy Database Wizard is shown as the first step of the wizard, so click the Next button:

   

5. In the Select a Source Server step, specify the source SQL Server instance and click the Next button:

   

6. On the Select a Destination Server step, specify the destination SQL Server instance and click the Next button:

   

7. Select the Transfer Method step allows users to select the transfer method and in this example the Use the detach and attach method option is selected and click the Next button:

   

8. Under the Select Databases step check which databases need to be moved or copied and click the Next button:

   

9. In the Configure Destination Database step specify database file names and whether to overwrite existing database at the destination and click the Next button:

   

10. In the Select Server Objects step, choose which objects to copy, and as shown, logins are selected by default:

    

    Note that the Select Server Objects step 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.

11. Configure the Package step creates an Integration Service package with properties that could be specified below or leave default ones and click the Next button:

    

12. Schedule the Package step allows user to Run immediately or Schedule the Integration Service package created in previous step. In this example, the Run immediately option is selected and the Next buttons is clicked:

    

13. The last step is the Complete the Wizard in which the complete summary of chosen actions in the Copy Database Wizard are shown and if everything is in order click the Finish button to initiate the copying of 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 panel expand the Security -> Logins node
   

2. Right-click the SQL login that needs to be migrated, and choose the Script Login as -> CREATE To -> New Query Editor Window command:

   

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: 3/19/2020 2:40:23 AM ******/
CREATE LOGIN [dnnuatuser] WITH PASSWORD=N'î覫ù±_K¨_þèDÙ=^R|''î_ƒK¤_¶qù[(', DEFAULT_DATABASE=[AdventureWorks2017], 
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:

In case of users with large number of SQL-authenticated logins every login needs 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, C# 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 follow these steps:

1. Run ApexSQL Script and the New project window will be shown. Specify a SQL Server, click the Connect button and check a desired database:

   

2. Switch to the Options tab and under the Structure section check the Login with user option and click the Load button:

   

3. Once a database is loaded, under the Structure view, scroll down through the object types, locate select users for migration and click the Script button to initiate the Script wizard:

   

4. In the first two steps of the Script wizard select the Structure scripting mode and SQL script output type:

   

   

5. Under the Dependencies step, just click the Next button as in this case there won’t be any dependent objects:

   

6. In the last step of the Script wizard, the SQL script options step, select the Open script in and editor option and click the Create button:

   

7. The script creates both the user and the corresponding login:

   

8. To execute the generated script on a destination SQL Server instance, click the Connect button and the Connect to SQL Server dialog will be shown where Server, Authentication type and Database fields should be entered and then click the Connect button:

   

9. After connecting to SQL Server and database, click the Execute button and the script execution messages will be shown in the Messages panel:

   

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