I recently discovered that my book (Pro SQL Server 2008 Mirroring) has an older version of the script to copy logins to the mirror partner for database mirroring. The final version of the script handles SQL logins as well as Windows groups and users. I’m really disappointed that the newer script didn’t make it into the book because I put a lot of work into it to make sure it handled SQL logins. There are two main issues with transferring SQL Logins. Foremost is the SID (security identifier) for the login. The SID must be transferred with the login or it won’t automatically map to the database user. Secondly, the password is not available in clear text, so you can’t just read it from the database.
This script addresses both of these issues. It creates the SQL login with the same SID and it uses the stored hash of the password to recreate the password using the HASHED property to indicate that we are supplying the password already hashed. The tricky part was getting the varbinary values for the SID and the password hash into string format. Fortunately, there’s an XML trick that makes that easy:
Begin Set @PasswordHashString = '0x' + Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)'); Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED, '; Set @SIDString = '0x' + Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)'); Set @SQL = @SQL + 'SID = ' + @SIDString + ';'; End
The full script can be downloaded here: dba_CopyLogins.sql
Ryan Adams
This is like bacon wrapped bacon! You even handled explicit permissions and I did not even think to do that. How you handled the SID and Password is pure genious! I used the sp_hexadecimal to convert them in my version. I’ve written a post about this but won’t be publishing it for some time, but I will certainly be linking back to this.
SQLSoldier
Thanks Ryan!! I don’t think I can get a better compliment than “This is like bacon wrapped bacon!”. 🙂
Christian
Great code!
May I also suggest adding code to address port number instead of instance name:
If CharIndex(‘,’, @PartnerServer) > 0
Begin
Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);
End
Else
Begin
Set @Machine = @PartnerServer;
End
SQLSoldier
Thanks for the suggestion Christian!! I’ll take that into consideration. Though now that I think of it, I’m wondering if I should just remove that block. My intent is that you pass in the name of the linked server it should us and backslash and comma are both acceptable characters in the name of a linked server.
Christian
I thopugh the intent of that block was to exclude the Local window logins from the partner server when querying PartnerServer.master.sys.server_principals. These logins wouldn’t exist on the principal server. That’s why mine was exiting with errors, and why I put in the code to accept the comma.
SQLSoldier
You’re right!! I was looking only at the If Else block and not following it down to the dynamic SQL. Thanks again!! I’ll make that update.
Automate SQL Logins to DR Site
[…] out that the publisher has the old code for the procedure so he blogged and provided the new one HERE. It does not handle changing the passwords for accounts that already exist, but it would be very […]
Yano
Hello ! Your book about SQL 2008 Dataabse Mirroring is very interesting. I have a production environment with SQL 2008 SP1 and a database mirroring solution. When I want to test your “dba_copylogins.sql”, it doesn’t run:
exec dbo.dba_copylogins @partnetserver = ‘wh1234’
Msg 7202, Level 11, State 2, Line 1
Could not find server ‘wh0603v’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
The nameserver is good. Why this message about “sp_addlinkedserver” ?
Thanks for your response
SQLSoldier
Thanks Yano!!
Did you also create the stored procedure dba_ManageLinkedServer (also in the book)? If the linked server doesn’t exist, the procedure uses this procedure to create the linked server.
You can also download the SQL script to create it here: http://www.sqlsoldier.com/wp/wp-content/uploads/Scripts/dba_ManageLinkedServer.sql
Pradeep Adiga
Bob,
This is a very useful script. Thanks!
Chris
When I try and run it in a sql 2008 management studio it gives me the following error on the Cast(” As XML).
Cannot call methods on int.
Am i doing something wrong.
Thanks
SQLSoldier
Chris, I can’t reproduce the error. Can you make sure that the code has the right single quotes in place (” instead of ”).
T-SQL Tuesday #15: Database Mirroring Automation | SQLSoldier
[…] Speaking of logins, I have already covered transferring logins in a different blog post: Transferring Logins to a Database Mirror. […]
DBA Nightmare: SQL Server Down, No Plans
[…] to make your recovery process either: automate login creation. Schedule a job to run weekly with Robert Davis’s login copy script and send the results to yourself via email. That way, at the very least, you’ll have the […]
Kev Riley
Great script, just got a few errors
Incorrect syntax near the keyword ‘ANY’.
Msg 50000, Level 1, State 1
Incorrect syntax near the keyword ‘ANY’.
Msg 50000, Level 1, State 1
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
so ran it with debug = 1, and got
GRANT_WITH_GRANT_OPTION ALTER ANY DATABASE To [DOMAINsomeserver$];
GRANT_WITH_GRANT_OPTION CREATE ANY DATABASE To [DOMAINsomeserver$];
GRANT CONNECT SQL To [DOMAINMy.Username];
GRANT CONNECT On ENDPOINT::[Mirroring] To [DOMAINthisserver$];
where domain and usernames have been obfuscated.
SQLSoldier
Thanks for the feedback Kev! That should be a simple fix.
SQLGuyChuck
The fix I did to get around the grant_with_grant_option was this alteration of the dynamic sql:
Select @PermState = PermState,
@PermName = PermName,
@Class = Class,
@LoginName = LoginName,
@MajorID = MajorID,
@SQL = CASE WHEN PermState = ‘GRANT_WITH_GRANT_OPTION’ THEN ‘GRANT’ ELSE PermState END + space(1) + PermName + SPACE(1) +
Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)
When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)
Else ” End
+ space(1) + ‘To’ + space(1) + QUOTENAME(LoginName)
+ CASE WHEN PermState = ‘GRANT_WITH_GRANT_OPTION’ THEN ‘ WITH GRANT OPTION’ ELSE ” END + ‘;’
From @Perms
Where PermID = @CurrID;
Thanks for the book and script Robert!
-Chuck Lathrope
SQLSoldier
Thanks Chuck! I really need to write a new version of this.
Aaron Bertrand : Troubleshooting Error 18456
[…] Note that this could also be a symptom of an orphaned login. After establishing mirroring, Availability Groups, log shipping, etc. you may have created a new login or associated a user with a login on the primary database. The database-level user information gets replayed on the secondary servers, but the login information does not. Everything will work fine – until you have a failover. In this situation, you will need to synchronize the login and user information (for one example, see this script from Robert Davis). […]
alextan
Great Article
i launch the script like this:
exec dbo.dba_copylogins @PartnerServer = ‘vmwp13b-db’
where the vmwp13b-db is the name of the target server where the logins should be copied.
I see “Command(s) completed successfully.” but actually no logins are copied to the destination… Why? Am I missing something?
The idea is to copy logins between alwayson members in a SQL2012 Availability group.
Thank you in advance.
Alessandro
SQLSoldier
Hi Alessandro. The process was set up to be run on the mirror/replicas/secondaries and @PartnerServer is the server from which it will copy logins. Does that make sense?
alextan
Thank you for the reply,
However I tried to run the script from the target server where the logins should be copied like this
exec dbo.dba_copylogins @PartnerServer = ‘vgwp13a-db’
(vgwp13a-db master server)
“Command(s) completed successfully.”
The servers have each other configured as linked servers. Is there any other requirement?
… but actually the logins have not been copied…
Thank you
SQLSoldier
What do you get if you run the following:
exec dbo.dba_copylogins @PartnerServer = ‘vgwp13a-db’, @Debug = 1;
And do you have permissions to see the logins on the other server?
DataQualityServices setup with AlwaysON | B3IT on Microsoft
[…] AlwaysOn doesn’t replicate any logins. I use a sp called dbs_CopyLogins, it can dowloaded here: http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror, is very useful since it copies the password […]
Andy
Thank you for the script it works great for the initial loading of logins. One issue I am wondering how you handled was password changes. I would like to automate the copying of logins from production. This script will pick up any logins added from the last run, which is nice. But if the password changes for an existing login this would not come over to the replicate. If you have handled this another way I would interested in hearing how you did it.
Thanks again!
SQLSoldier
Thanks Andy. I made the decision not to handle “changes” and only process things that don’t exist. So many things can change that it would make the script very difficult.
Andy
Thanks for the reply. I think I am going to come up with something based off the updatedate column in syslogins. Its not the most elegant solution but seems to be fairly foolproof just for making sure I always have the latest password.
SQLSoldier
I know that Jonathan Kehayias was at one time trying to come up with a dynamic solution that would use extended events to transfer changes in real time, but there were issues with trying to get the required info. I don’t recall what the issues were.
Copy logins between SQL Server instances | Tomas LindTomas Lind
[…] 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. […]
Invoke-Sqlcmd : Exception of type 'System.OutOfMemoryException' was thrown
[…] have reused a solution from a login copy script written by Robert L. Davis (T | B): based on this article, I rewrote the script as […]
Robert Davis' Login Sync Script [Free DBA Tool of The Week] - SQL Server Consulting - Straight Path Solutions
[…] still works great. Check it out here in his blog post. Even if you don’t use his script to do this, it still will teach out a bit about some of the […]
Sync SQL Logins and Jobs - Ryan Adams Blog
[…] Transferring Logins […]
Synchronizing Logins And Jobs – Curated SQL
[…] Transferring Logins […]