Transfer Logins Between SQL Server 2005 Instances

Posted by & filed under , , , .

I’ve never been a fan of the SQL Server 2005 Integration Services task for transferring logins between servers.  It seems that I always misconfigure it or something gets missed.  And when I just want to move a single login its more effort to setup the task than it is worth.  What I’m really saying here is I’m not a fan of the task and I don’t plan to get "gooder" at using it.

Especially when there is a much better alternative… scipting the login with the password.  The link has a script that creates a couple stored procedures that, when executed, provides either a single or all logins with their properties.

The output script looks something like this:

– Login: ExistingUser
CREATE LOGIN [ExistingUser] 
WITH PASSWORD = 0x0100DE1894107F53A6ABF5436BA4F2A6BFFC0C51C156760E70F2 HASHED, 
SID = 0xDA25EBDBF88087D98B65D94B1DF3155B, 
DEFAULT_DATABASE = [master], 
CHECK_POLICY = OFF, 
CHECK_EXPIRATION = OFF

Very useful indeed…