Tuesday, April 1, 2008

Need to transfer logins from one SQL 2005 instance to another SQL 2005 instance? Here's how!

In SQL 2000 there was a DTS package that could do this. I didn't like DTS and never used it, but it is out there. I used the Microsoft-provided sp_help_revlogin stored procedure instead to do this in 2000, but when I looked at the code I realized it would need an update for 2005.
In 2005 the sys.syslogins view exits, but you really shouldn't use it- you should use sys.server_principals (see books online). So, when I looked at that system view I realized this was really going to take some work... so naturally I Googled! ;)

Anyways, I found out that good-ol' MS had already updated the TSQL that generates sp_help_revlogin (and it's required binary stored proc) and posted it online in KB 918992. I copied the code and it worked just like it always had except that I was able to transfer the logins with SIDs and passwords from one 2005 instance to another instead of a 2000 to 2005 or 2000 to 2000 instance. :)

http://support.microsoft.com/kb/918992