Copy logins between SQL Server instances

Microsoft SQL Server

If you are using SQL Server log shipping (or mirroring), you probably want to copy the logins from the primary server to the secondary.

When databases are log shipped, the users go along with their respective databases. But those users are connected to a login on the primary server. Those logins are not copied automatically to the secondary. Users without connection to a login are called orphaned users.

This has the effect that users may not be able to login on the secondary in case of a failover.

One solutions to this problem is to transfer logins manually. That may be the best solution if there are very few logins to manage. But if there are more than just a few logins, an automatic solution is required.

There are many methods available to handle this. In this blog post I’ll combine a couple of the best solutions available, and add a few features that I found missing.

I’ll start by showing the best existing solutions to copy logins. After that, I’ll present my own solution that is a combination of the best features from two of them, along with some minor additions from me to make the procedure cause less errors.

SSIS Transfer Logins Task

Among the more popular methods to transfer logins to another SQL Server instance is to use the SSIS Task “Transfer Logins”:

SSIS Transfer Logins Task

SSIS Transfer Logins Task

The options as showed in the picture above are pretty self explanatory, except perhaps for the CopySids option. The SID is short for “Security Identifier” and is used internally by SQL Server to map logins to users. If the SID’s for logins and users match after the transfer to the secondary, the login and user are mapped. If not, a mapping must be done with the system stored procedure sp_change_users_login.

This is perhaps the easiest way to transfer logins between servers. But if you have different requirements than what can be accomplished with the task in SSIS, you are out of luck. The only options you have are those shown in the picure above.

sp_help_revlogin

This is a stored procedure from Microsoft Support that can be found here. The procedure is not included in SQL Server, so you need to create it yourself.

sp_help_revlogin returns a script with SQL statements to create the logins existing on the server you execute the procedure on.

You’ll still need to set up the execution of the procedure somehow to automate the transfer of logins between the servers.

sp_help_revlogin will not transfer roles and permissions, only logins. This can definatly be a problem, since those object control what the user can do in a database. I strongly suggest you make sure to transfer roles and permissions as well in a log shipping solution. So, enter solution number three, the stored procedure dba_CopyLogins, my personal favorite.

dba_CopyLogins

This procedure by SQLSoldier does what sp_help_revlogin does, with a couple of very important additions. It also copies roles and permissions. Also, it has a more elegant solution for handling the hash strings using XML. The script can be found here. Also check out this blog post.

However, there were a couple of changes that I wanted to make that resulted in the stored procedure ScriptLogins.

ScriptLogins

This procedure ScriptLogins is basically the same as dba_CopyLogins, but with a few modifications.

Drop and Recreate

ScriptLogins checks if the login already exists. If so, it drops and recreates it. This way any password changes are also managed.

More filters on system users

I filter out a couple of additional system users: ‘BUILTIN%’ and ‘NT %’.

Filter out users no longer in the network

If a login exists on a SQL Server instance, and the corresponding account has been dropped in the AD, the login will remain in SQL Server. But if you try to create a login based on a non-existing account you’ll get an error:

Msg 15401, Level 16, State 1, Line 1 Windows NT user or group ‘XXX\NNN’ not found. Check the name again.

To avoid this error, I filter out users dropped from the network with the help from system stored procedure sp_validatelogins, as described in an earlier blog post.

Default database

ScriptLogins checks if the default database exists on the secondary. If not, master becomes default. If you try to set the default database to a database that doesn’t exist, you’ll get the error:

Msg 15010, Level 16, State 1, Line 1 The database ‘XXX’ does not exist. Supply a valid database name. To see available databases, use sys.databases.
Renamed SA

The original filter was “name <> ‘sa'” to filter out the sa account (which you don’t want to overwrite on the secondary). A common practice however, is to change the name on the SA account. To avoid transfering the sa account, the filter can be set as “sid <> 0x01″ instead, since the sa account always has this SID even if the name is changed.

Ok, enough talk. Here is the script: ScriptLogins. Create it in your master database on the primary server, or better your “dba-database”. As always, run at your own risk, and remember to test. Also, I am not claiming that this is better than sp_help_revlogin or dba_CopyLogins. But it has a few changes, and you’ll need to choose which one suits your demands better. Or create your own ūüôā

To use the procedure, I suggest you set up a scheduled SQL Agent job on the secondary that runs the following T-SQL statement:

DECLARE @StatementToRun VARCHAR(MAX)
SELECT @StatementToRun = StatementToRun FROM OPENQUERY("linkedservername", 'EXEC master.dbo.ScriptLogins')
EXEC (@StatementToRun)

Replace “linkedservername” with you linked server name. If you havent already a linked server to the primary from the secondary, you’ll need to fix that first. Also, replace the master database with whatever database you created the procedure in.

And finally, a warning. The existing logins on the secondary with a matching name with those transferred will be overwritten! That is the major difference from the other solutions I presented. The other solutions will raise an error, but ScriptLogins will drop and recreate instead.

27 comments » Write a comment

Leave a Reply

Required fields are marked *.