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:
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.
To use the Copy Database Wizard for migrating SQL logins:
On the Select Server Objects page, where you choose which objects to copy, logins are selected by default:
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.
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:
Right-click the SQL login you wish to migrate, 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: 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:
In case of users with a large number of SQL-authenticated logins every login need to be scripted, passwords reset, and enabled manually.
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.
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:
In the Results grid select users for migration and click the Script button:
Follow the Script wizard steps until the script is created. The 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:
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