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
Loading ...
Twitter
LinkedIn
TLF
RSS
WritersCafe
SQLPASS
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.
[...] 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 [...]
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 ”).
[...] Speaking of logins, I have already covered transferring logins in a different blog post: Transferring Logins to a Database Mirror. [...]
[...] 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 [...]
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.