Migrate Logins and Passwords across Database Instance
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.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
You may also like
let us simplify it
select logins from SSMS under security and press F7 select all logins and right click select SCRIPT LOGIN AS –> CREATE To –> NEW QUERY EDITOR WINDOW copy the script and execute in other instance you get the same
Hi Venu, through Object Explorer Details you can generate script for login but the login is created disabled and with a random password for security reasons. Thanks for sharing comments.