2010-02-01

Copy SQL login

In my daily surroundings we have a lot of mirrored SQL2005 databases, and to be ready to fail over we do a daily compare of the instances in each mirroring setup.
Among other things we compare the logins, and is a SQL login missing on the mirror instance, it must be created. It could be nice to have a single statement to do this...
The obstacle has been that the password hash is type varbinary(max), but using my favorite internet search engine inspired a solution.

The basic statement is
DECLARE @login_name sysname;
SET @login_name = N'Siegfried';
DECLARE @pw_hash varbinary(max);
SELECT @pw_hash = [password_hash] FROM master.sys.sql_logins WHERE [name]=@login_name;
SELECT 'CREATE LOGIN ['+@login_name+'] WITH PASSWORD=0x'+ CAST('' as xml).value('xs:hexBinary(sql:variable("@pw_hash"))','varchar(max)') + ' HASHED;' AS [DCL];

The value of the variable @login_name must be entered manual, but this is the basic statement...

Discussion
The statement could be implemented in a single function (UDF), procedure (USP) or a complete procedure.
This depends on the given database infrastructure. Among other things the remoting possibilities must be take in consideration.

Reference
The inspiration is the blog entry at "SQL Server Engine Tips":
http://blogs.msdn.com/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
Also the documentation of [sys].[sql_logins] at MSDN Library:
http://msdn.microsoft.com/en-us/library/ms174355(SQL.90).aspx

No comments: