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 and passwords 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 and passwords between instances of SQL Server

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

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

[sourcecode language=’sql’]SELECT principal_id, sid, name
FROM sys.server_principals[/sourcecode]

[sourcecode language=’sql’]SELECT sid, name
FROM sys.syslogins[/sourcecode]

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.

[sourcecode language=’sql’]SELECT sid
FROM sys.server_principals
WHERE name = ‘server_login1’

USE AdventureWorks2012
GO
SELECT sid
FROM sys.database_principals
WHERE name = ‘db_login1′[/sourcecode]

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.