During a recent migration from SQL 2000 to SQL 2008, I needed to move logins from the old server to the new server. We use mixed mode authentication, so we had both Windows and SQL logins that needed to be moved.
My first attempt was using SSIS. I created a package on the 2008 server and used the Transfer Logins task to move the logins to the new server. Piece of cake. I set it up, made sure to specify that the SIDs needed to be transferred as well, ran it and it worked fine. Then I went to test the logins and ran into a problem. All the Windows logins worked, but the SQL logins did not. Turns out, BOL says the Transfer Logins task will transfer SQL logins, but it changes their password to a random string - and doesn't tell you what that string is. I'm sure it does this as a security precaution, but really, this makes this task pretty much worthless, in my opinion. If I need to go and manually change the passwords of all the SQL logins I just transferred, I may as well create them from scratch in the first place. Furthermore, many companies, like mine, have programs using SQL logins whose passwords have long been forgotten. Sure, I could hunt down the programs using them and try to find the configuration files that likely have the password in them, but that's a time consuming process. And as a DBA, my area of knowledge doesn't extend to configuring dozens of programs, each of which might store its configuration data in a different way, so I would need to pull in other resources in I wanted to go this route.
Luckily, there is another way. Microsoft has supplied some scripts to transfer logins, namely sp_hexadecimal and sp_help_revlogin. I created and ran these procedures on the 2000 server, executed the output on the 2008 server and... the logins still didn't work. Hmm. A Google search showed lots of people were experiencing this same problem when they tried to move logins between SQL 2000 and 2008. Some claimed the two procedures mentioned before worked, but they didn't for me.
It turns out, I just needed to read a little more carefully and think a little bit. SQL Server changed the way it hashed passwords between versions, so going straight from 2000 and 2008 won't work. But, as they say, there is more than one way to skin a cat.* Method 2 of this Microsoft KB article details how to transfer a login from SQL 2000 to SQL 2005. And this Microsoft KB article details how to transfer a login from SQL 2005 to SQL 2008. I happened to have a SQL 2005 server hanging around, so I transferred the logins to there, then transferred them from there to the 2008 server. That worked!
If you decide to do this, there are a couple of things to watch out for. The script will set logins to have the database roles and default databases they have on their original server. Those might not exist on the intermediate server, or even the final server, so you'll want to examine the output scripts of the procedures and remove any references to databases or roles that do not exist on the destination server. And watch out for logins that might already exist on the intermediate server. To keep things tidy and mitigate security risks, be sure to delete the logins from the intermediate server after you are done.
* At what point in history was cat-skinning so popular that more than one method was needed?