How to Transfer Logins and Passwords?

There are various reasons why DBA moves databases from one SQL Instances to another. The purpose of the move is mainly to upgrade hardware and software for better performance and security. Why do you do database migration is out of scope of this article? With this article, you will learn one of the many steps which is performed while moving SQL databases to another SQL Instance. After moving databases to another Instance, you will also need to move the users log in, password and resolve any orphan users. If you miss this step, the users may not be able to log in to the new sever, instead they will receive the following error message.

Login Error

Msg 18456, Level 16, State 1
Login failed for user '%ls'. 

There are two ways you can transfer Login and Password to another Instance.

1. Using DTS Package
2. Script from Microsoft.

DTS is very easy and common ways to transfer users and their log in when you are in a same network . What if you the destination SQL Instance is not in your network? This may be a problem if you know the DTS package approach of moving users. With this article, we are going to use the script approach which is network independent. This script is provided to us from Microsoft and I have a link to that as we will have the updated version at all time.

Get the Script appropriate for your SQL Server Version HERE>>

1. Run the script on your source SQL Instance, The script will create sp_help_revlogin stored procedures.
2. Run the stored procedures on source Instance and capture the Output ( EXEC master..sp_help_revlogin)
3. Copy the output from step 2 and paste the script on a destination SQL Instance Query Analyzer and run the script.

The logins and passwords are now transferred to new instance and is ready to for the user to use. If you see the users transferred but unable to logged in using their credentials. You might need to check the roles, privilege or they might be orphaned.