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.

Transfer Login and Password

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...

11 Responses

  1. venu says:

    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.

  2. tajuddin says:

    pls send me use of sp_who and sp_who2

  3. bharat says:

    plz upload upgradation migration videos,differences also

  4. pradeep says:

    when i’m trying to execute sp_help_revlogin ….i’m getting an error like

    Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
    Invalid object name ‘master..sysxlogins’.

    what’s wrong in that query can you help me!!

Leave a Reply

Your email address will not be published.