How to Transfer Logins from one SQL server instance to another SQL Server instance?

It is one of the common question, which we can expect during any SQL Server interview: “How should you transfer logins from one instance of SQL Server to another?” The answer is pretty simple, however, its very complicated as well. Its complicated until you know, what and how to do? Once you know the concept, its as simple as you can do it while you are sleeping…. joking, as don’t work while sleep. Sleep…. Wake up and then work !!!

Note: This topic is primarily focused to SQL Server 2005 or higher versions, however, necessary links will be mentioned for earlier versions of SQL Servers.

It’s a common misunderstanding that, logins are copied from one instance of SQL Server to another, while we restored the database. When we restored a database, the restoration process copied all the database objects (like tables, views, stored procedure, triggers, user-defined functions, etc.) along with the database user accounts. This topic is primarily focused to SQL Server 2005 or higher versions, however, necessary links will be mentioned for earlier versions of SQL Servers. The SQL Server login details are stored in the master database. Microsoft PSS team has created a script to copy the logins from one instance to another. The above script generates the login details along with the encrypted password. In the next part, we will discuss how exactly this will work.

Steps to be followed:

1. Click on the link : http://support.microsoft.com/kb/918992/. Copy the script available in the website and execute it on the source instance. The script available in the given link can be run on a SQL Server 2005 database instance.

 

2. Once the script is executed on the source instance, two stored procedures are created on the master database. They are:

i. sp_hexadecimal

ii. sp_help_revlogin

3.  To create a script including all the logins of the instance along with the SIDs and Passwords, execute the procedure “sp_help_revlogin” on the source instance. Copy the output script generated by the stored procedure "sp_help_revlogin" and execute it on the destination instance.

4. The logins will be created on the destination instance. The above task is completed.

Note: Execute the script from the following link: http://support.microsoft.com/kb/246133, if you want to copy the logins from the following instances:

a. Between two SQL Server 7.0 instances

b. Between SQL Server 7.0 to SQL Server 2000

c. Between SQL Server 2000 instances

About these ads