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

This post was cross-posted from the main SQLSoldier blog. Please visit the source blog to leave a comment: http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror