Transferring Logins to a Database Mirror
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
Twitter
LinkedIn
TLF
RSS
WritersCafe
SQLPASS
Facebook
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.
Thanks Ryan!! I don’t think I can get a better compliment than “This is like bacon wrapped bacon!”.
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
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.
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.
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.
Pingback: Automate SQL Logins to DR Site
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
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
Bob,
This is a very useful script. Thanks!
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
Chris, I can’t reproduce the error. Can you make sure that the code has the right single quotes in place (” instead of ”).
Pingback: T-SQL Tuesday #15: Database Mirroring Automation | SQLSoldier
Pingback: DBA Nightmare: SQL Server Down, No Plans
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 [DOMAIN\someserver$];
GRANT_WITH_GRANT_OPTION CREATE ANY DATABASE To [DOMAIN\someserver$];
GRANT CONNECT SQL To [DOMAIN\My.Username];
GRANT CONNECT On ENDPOINT::[Mirroring] To [DOMAIN\thisserver$];
where domain and usernames have been obfuscated.
Thanks for the feedback Kev! That should be a simple fix.
Pingback: Aaron Bertrand : Troubleshooting Error 18456
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
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?
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
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?
Pingback: DataQualityServices setup with AlwaysON | B3IT on Microsoft