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”:
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:
[red_box]Msg 15401, Level 16, State 1, Line 1 Windows NT user or group ‘XXX\NNN’ not found. Check the name again.[/red_box]
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:
[red_box]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. [/red_box]
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.
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.
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.
There are filters in the script that excludes logins that you dont want to remove. Just add more filters there.
Thank you, good job.
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
Thanks Magnus for the feedback, and also for providing a solution!
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.
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 Emma, can you run the procedure on the primary? That is, run only this statement:
EXEC master.dbo.ScriptLogins
/Tomas
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.
Hi Thomas.
One more method of “Copy logins between SQL Server instances”.
Use PowerShell to Migrate SQL Server Instances (db, logins, jobs, etc) 🙂
https://gallery.technet.microsoft.com/scriptcenter/Use-PowerShell-to-Migrate-86c841df
Thanks for the link!
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
Hi, thanks for the feedback! Really useful 🙂
When I’m executed:
SELECT @StatementToRun = StatementToRun FROM OPENQUERY([SERVER], ‘EXEC [MASTER].dbo.[ScriptLogins]’)
from the remote server I got the error:
Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because every code path results in an error; see previous errors for some of these.
Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]
Could not find stored procedure ‘MASTER.dbo.ScriptLogins’.
Changing the query to:
SELECT @StatementToRun = StatementToRun FROM OPENQUERY([SERVER], ‘EXEC [MASTER].dbo.[ScriptLogins] WITH RESULT SETS (([StatementToRun] nvarchar(max)))’)
and I still get the error:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘MASTER.dbo.ScriptLogins’.
I can execute simple selects against the remote server with no problem. It executes fine on the server where it’s located.
link to the script is broken.
Works for me? Are you using this link?
Thank you Soooo much for this amazing script
You made my day
Best Regards
Hi Tomas the SQL Script (ScriptLogins.txt) is exactly what I need to sync Logins from Primary to Secondary Log Shipped Instances..
The only issue I have is VARCHAR(MAX) is not big enough to store the full script.
If I do it manually via a Linked Server;
EXEC [LinkedServer\Instance].[DBAMaintenance].dbo.ScriptLogins
Then run the output Script manually it work perfectly.
However I want to run this daily via a SQL Agent Job, any suggestions?
Thanks
Mark
Hi Tomas, I would like to run your scriptlogins script to transfer logins, roles and permissions from a SQL Server 2000 instance to SQL Server 2008.
When I run the script on the source instance I get the error message “Incorrect syntax near ‘.’ for the two lines
SET @PasswordHashString = ‘0x’ + CAST(” AS XML).value(‘xs:hexBinary(sql:variable(“@password_hash”))’, ‘nvarchar(300)’)
SET @SIDString = ‘0x’ + CAST(” AS XML).value(‘xs:hexBinary(sql:variable(“@sid”))’, ‘nvarchar(100)’)
What would be the correct syntax to use against a SQL Server 2000 instance?
I hit the same error as Magnus but came up with my own solution (because I hadn’t read the comments, Doh!) which was simply to add a ‘\’ to the strings which will work provided you don’t have a user name the same as the server name:
AND SUBSTRING(principals.[name], 1, ABS(CHARINDEX(‘\’, principals.[name]+’\’)-1)) SUBSTRING(@@SERVERNAME, 1, ABS(CHARINDEX(‘\’, @@SERVERNAME+’\’)-1))
also my site frowns on linked servers so it was easier to run a job which created the script on a shared drive and pick it up from there. The command was:
sqlcmd -Q “exec scriptlogins” -h -1 -o “c:\temp\scriptlogins.txt” -y0
Thanks for helping with a solution