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.

image 

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.

image

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

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Security in SQL Server. Bookmark the permalink.

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

  1. Pingback: A year with SQL…. Still Learning… | SQL…. Still Learning

  2. Bikram Pattanayak says:

    Nice one for a Fresher….

  3. uacsux says:

    Worked for me. Thanks!

  4. CHT says:

    Thank you for giving instructions on how to do this. Every site I found was here is code like we are all SQL gods. This was perfect and it worked!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s