How to Transfer Logins to Another SQL Server or Instance

With any server migration, ideally you want things to run smoothly and re-creating SQL Server logins from scratch is not something you really want to or should be doing.

I am currently involved in a complete server replacement for one of our SQL Server clusters. In this environment, mixed authentication is enabled.

Back in the day when I was learning about transferring databases from one server, I quickly discovered that restoring a database from one server to another was not as straightforward as I had anticipated. I came across a hurdle which I needed to overcome in the form of the SID’s.

So first I’ll talk about the ways in which you can transfer logins from one SQL Server to another and explain about the SID’s further down the post.

Ways to transfer SQL logins

Now you can do this the easy way or the hard way! 🙂

You can manually set up copies of your logins on your new server with passwords (if you know them) or you can automate this task with the help of either a T-SQL script or SSIS.

I don’t know about you but I don’t like manual and I would recommend that you automate this each time.

Using T-SQL to transfer logins between SQL Server instances

You can obtain a script from Microsoft here. You run the script, it creates a stored procedure called sp_help_revlogin on your SQL Server. When you run the stored procedure, it outputs CREATE LOGIN statements for all your server logins including their passwords and “sids”.

You can then take that script, run it on your new SQL Server to set up the server logins there. Obviously taking care to look over it before you execute it to make sure that it has captured everything and to ensure that you are not copying over anything that you do not need on your new instance.

The script will set up the logins on your new server in using the default language configured for that server so make sure that is configured correctly first.

Using the transfer logins task in SSIS to copy logins between SQL Server instances

There is a transfer logins task within SSIS which will also do the job. In the screenshot attached, I am using the new version of SSIS which ships with SQL Server 2012. The transfer logins task falls under “other tasks” in the the SSIS toolbox and is available in older versions of SSIS.

transfer logins between sql server servers or instances

So hopefully you can see from the screenshot that the transfer logins task is fairly self explanatory. The great thing about SSIS is that it holds your hand in many ways and makes things as easy as possible.

You can create connections from this task, enter the login details for your source and destination, choose which logins to copy and you can configure the task to behave in different ways when the login exists.

As you can see I have circled “CopySids” so now is a good time to write about them in more detail.

So what is a SID?

SID is short for “security identifier” A SID is an internal id which gets assigned to a server login when the login is created.

The SID can be viewed by querying the sys.server_principals system view or you can also view it by looking at sys.syslogins

SELECT principal_id, sid, name
FROM sys.server_principals
SELECT sid, name
FROM sys.syslogins

Each server login, be that a SQL login, Windows login or one which was created using a certificate or asymmetric key, gets assigned a SID. So lets imagine an example scenario where a new login is created and it happens to be a SQL Server login, that login gets named “server_login1”.

You then create a database login inside the AdventureWorks2012 database using the server login just created. Lets call that “db_login1”.

Now view the SID’s in sys.server_principals and sys.database_principals from AdventureWorks2012 and the SID will be of the same value.

SELECT sid
FROM sys.server_principals
WHERE name = 'server_login1'

USE AdventureWorks2012
GO
SELECT sid
FROM sys.database_principals
WHERE name = 'db_login1'

Unless you create your logins on your new server using a method such as the sp_help_revlogin or the SSIS transfer logins task with “CopySids” enabled, your logins will be created with new SIDs.

I am not sure whether it is possible that your new server will ever assign the same SID but I do know that it is very unlikely. Perhaps you have more chance of winning the lottery jackpot 🙂

So what problems does this cause?

As the SIDs are different, when you come to copy across/restore your databases from your old server, the database logins simply won’t work as they will be orphaned from their original SID.

You may encounter this error for example if you try and add a server login to a database which you have just copied across.

Error 15023: User already exists in current database

This will then need to be corrected.

There is a procedure that you can run in order to do this and it basically takes the server login and database login and marries them up by name with the same SID.

This procedure is called sp_change_users_login and I’m going to talk about that in this post.

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Comments

  1. Very useful information. I ended-up using a script because I changed the name of the database during my restore. At least I think this was my problem – SQL Server 2008 is new to me. I added a link to this article on my site.

    Thanks,
    Charles

    • Andy Hayes says:

      Hi Charles
      Thanks for the link and your comments on this post.

      All the best.
      Andy

      • hi,
        i want to transfer the database and login from mssql server 2012 std to mssqlserver 2012 ent. is is possible. if yes how can i do this. i am very new to mssql don’t have a coding background.
        thanks

  2. Hi Andy,

    Please let me if we can also the issues over here that are i met in my environments. if yes how i should need to add into the links and bogs . That will would be very helpful for the people.

  3. pruthviraj says:

    the transfer logins will change the password. i have faced that issue , so it’s better to take the backup up of the logins.

    so i have written a query to get that.

    https://gallery.technet.microsoft.com/COPY-LOGINS-IN-SQL-SERVER-a15f60b5

Trackbacks

  1. […] Here is another on how to create an SSIS SQL Agent job that will sync the logins on a scheduled basis: How to transfer Logins via SSIS Jobs […]

  2. […] other option is to set up a SSIS package following this blog post and call it from Powershell as […]

Speak Your Mind

*


*