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.

19 comments » Write a comment

  1. Thanks for sharing this Tomas. I have two questions.

    1. How would you handle logins that no longer exist in the primary database? Is there a way to script out the deletion of logins from the secondary if they no longer exist in the primary?

    2. Do you have a good reference for linking a secondary server to my primary one? I running SQL Server 2005 and plan on upgrading to 2012 later this year.

    • 1. If you have the Primary set up as a linked server from the Secondary, you can compare logins between them and delete those on Secondary that has no match in the Primary. I’m guessing this would be the easiest solution. If that won’t work for you, a more complex solution would be to capture the deletion of logins with a database trigger (that fires on DROP LOGIN) on the Primary and store those logins in an admin database or likewise. Then use the result of that on the Secondary to iterate and delete. This would be a better solution if you are unsure on the origin of logins on the Secondary. Perhaps if they were added manually.

      2. Here: Linking Servers. There are version for both SQL 2005 and 2012 on that page, but as far as I know, the procedure to set up a linked server hasn’t changed at all since SQL 2005.

  2. hi Tomas,

    there’s one thing that is not synchronized with your script. If you remove a login (AD or sql logins) from the primary server, the secondary server will still have those logins. Would it be possible to also synchronize deleted logins?

    • Hi Alexandre! You are right, deleted logins are not handled in my script. The reason why is because there is no 100 % correct solution to this. See my answer in a comment above. The first solution would work fine (look for logins no longer existing in the primary) if there is only one source to the logins. It could check more primary servers however, but that’s an unknown and not something I can handle in my script. Also, there can always exists logins manually added on the secondary that are not supposed to be deleted.

      Sure, all those scenarios could be covered by dynamic behavior and settings in the script, but it gets pretty complex.

      Dropping the “logins” from the AD will work however. If a login is removed from the AD, the login will be dropped from the secondary. But I realize this is not always possible.

      • oh.. sorry i didnt see the comments above that already answered my question. I think the best way would be a setting to enable/disable the deletion of logins on the secondary server and another setting to exclude logins that you don’t want to be removed.

        do you have something that you can give me to help me start this feature? If i can make it work on my side, i will give you back the script.

  3. I think I found an error in the scriptlogin-version.

    I had a server that started with capital S and a server login with a name starting with s. Neither of these two contained any ‘\’-characters.

    this resulted in the line
    ‘SUBSTRING(principals.[name], 1, ABS(CHARINDEX(‘\’, principals.[name])-1)) SUBSTRING(@@SERVERNAME, 1, ABS(CHARINDEX(‘\’, @@SERVERNAME)-1))’ not working as intended as it compares ‘S’ ‘s’, which evaluates to false (i guess this could depend on the collation). Even if the collation is fixed, in my situation the comparison should be between the full servername and the full loginname since neither of the contain any ‘\’-characters.

    I propose a solution in sql server 2012 and 2014 as

    IIF( charindex(‘\’,principals.[name]) = 0 , principals.[name] , substring(principals.[name],1,charindex(‘\’,principals.[name]) – 1) ) IIF( charindex(‘\’,@@SERVERNAME) = 0 , @@SERVERNAME , substring(@@SERVERNAME,1,charindex(‘\’,@@SERVERNAME) – 1) )

    this problems comes from charindex() returning zero for strings that dont contain any ‘\’-character

      • Considering your script was exactly what I was looking for, it was the least I could do. Please do note that my fix requires sql server 2012 or later (the sql function IIF was introduced with 2012 if I’m not mistaken).

        Anyway, thanks for script.

  4. Hi Tomas. I got an error on initial part of script (Run from the instance that the logins will be created on:
    DECLARE @StatementToRun VARCHAR(MAX)
    SELECT @StatementToRun = StatementToRun FROM OPENQUERY(“linkedservername”, ‘EXEC master.dbo.ScriptLogins’)
    EXEC (@StatementToRun)

    but was able to fix it by running DECLARE @StatementToRun VARCHAR(MAX)
    exec “DFC14\DFC14”.master.dbo.ScriptLogins instead. It was apparently because of an issue running select statements on linked server.

    I then stumbled across an error adding logins/roles/permissions to script ;

    Msg 137, Level 15, State 1, Line 186
    Must declare the scalar variable “@TotalRows”.
    Msg 137, Level 15, State 2, Line 187
    Must declare the scalar variable “@CurrentRow”.
    Msg 137, Level 15, State 1, Line 188
    Must declare the scalar variable “@sid”.
    Msg 137, Level 15, State 2, Line 191
    Must declare the scalar variable “@type”.
    Msg 137, Level 15, State 2, Line 192
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 1, Line 205
    Must declare the scalar variable “@PasswordHashString”.
    Msg 137, Level 15, State 1, Line 206
    Must declare the scalar variable “@SIDString”.
    Msg 137, Level 15, State 2, Line 207
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 218
    Must declare the scalar variable “@is_policy_checked”.
    Msg 137, Level 15, State 2, Line 219
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 222
    Must declare the scalar variable “@is_expiration_checked”.
    Msg 137, Level 15, State 2, Line 223
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 226
    Must declare the scalar variable “@denylogin”.
    Msg 137, Level 15, State 2, Line 227
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 229
    Must declare the scalar variable “@hasaccess”.
    Msg 137, Level 15, State 2, Line 230
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 232
    Must declare the scalar variable “@is_disabled”.
    Msg 137, Level 15, State 2, Line 233
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 236
    Must declare the scalar variable “@CurrentRow”.
    Msg 137, Level 15, State 1, Line 241
    Must declare the scalar variable “@TotalRows”.
    Msg 137, Level 15, State 2, Line 242
    Must declare the scalar variable “@CurrentRow”.
    Msg 137, Level 15, State 1, Line 243
    Must declare the scalar variable “@RoleName”.
    Msg 137, Level 15, State 2, Line 246
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 249
    Must declare the scalar variable “@CurrentRow”.
    Msg 137, Level 15, State 1, Line 254
    Must declare the scalar variable “@TotalRows”.
    Msg 137, Level 15, State 2, Line 255
    Must declare the scalar variable “@CurrentRow”.
    Msg 137, Level 15, State 1, Line 256
    Must declare the scalar variable “@LoginName”.
    Msg 137, Level 15, State 2, Line 259
    Must declare the scalar variable “@StatementToRun”.
    Msg 137, Level 15, State 2, Line 264
    Must declare the scalar variable “@CurrentRow”.
    Msg 137, Level 15, State 2, Line 266
    Must declare the scalar variable “@StatementToRun”.

    Has anyone else had this problem? Thanks

      • Hi Tomas thanks for getting back to me so quickly. I can run that statement successfully on the primary

        Thanks,

        • An I guess that the proc returned a SQL script?

          I think the errors seem strange… However I would go back and make sure the linked server is set up correctly. The syntax servername.master.dbo.ScriptLogins uses the linked server anyway. Check the RPC Out setting.

          /Tomas

          • Hi Thomas , thanks for your help…

            both rpc settings are set to true and I have tested linked connection successfully on both sides. The linked server is between an alpha instance and its beta instance so both instances mirror each other and are joined using an availability group

            . If I run servername.master.dbo.ScriptLogins or EXEC master.dbo.ScriptLogins alone I get “command completed successfully”.

            I then get same error trying to add logins/roles/permissions to the script.

  5. Hi Thomas,
    Really helpful script!
    Few thing that might make it even better:

    1) If machine is renamed, and sys.servers is not updated, script will not detect correctly local windows logins.
    Please use SERVERPROPERTY(‘MachineName’) instead of @@SERVERNAME in WHERE condition. That will make script resillient to server rename.
    2) comparison of strings before “\” looks like it needs to be improved
    3) Add option to just print the output. It is useful for one-time migrations or when linked server between the two is not possible. The problem is to print the very long string without truncation. I have a procedure for that if you need it.

    Best regards,
    Vedran

Leave a Reply

Required fields are marked *.