As a part of SQL Server migration we need to migrate all the logins and passwords from one SQL Server instance to other SQL Server instance or you can also transfer specific login and password to other instance for that we will use microsoft knowledge base 918992 article which is normally used for SQL Server 2005 and above where microsoft has provided excellent script to create procedures which generates existing server credentials. This script is externally provided and it is not available inbuilt with Microsoft SQL Server system procedures. You have other method to migrate logins across instance with SSIS also but here we are going to refer with query only.
I will show you example how to migrate SQL Server logins and passwords in a simple way.
You need to follow below steps:
1. Create procedure sp_hexadecimal and sp_help_revlogin in master database on first instance.
2. Execute procedure sp_help_revlogin on first instance.
3. Copy the output of step 2 and paste to query editor in new instance.
4. Execute CREATE LOGIN scripts on new instance.
5. Now try to connect SQL Server with your created logins.
Basically this process generates login and password with hashing algorithm and provides actual security identifier (SID) used in the instance.
Figure 1: Generate Logins and Passwords
To create sp_hexadecimal and sp_help_revlogin procedures you have to copy query from microsoft support site. You can click on this link https://support.microsoft.com/en-us/kb/918992
Note: you may get login error if default database is not mapped to login or you may need to correct orphan users in some cases.
