How to transfer logins and passwords

A couple of years ago, when I was a very junior DBA, we upgraded our main database (and everything else on the server), which contained all the information about everything at the University that employed me at the time, (students, faculty, staff, courses: you name it, it was in there) from SQL 2000 to SQL 2005, moving it across servers at the same time. This database was used by many members of staff on a daily basis, via a custom built Delphi interface. When it came to migrating all the usernames and passwords, we made use of this interface to log the users' keystrokes to find out their passwords, and input this all into a great script to recreate them on the new server. Where a user had not changed their password from the inital password given, or where a user's password did not fit the new server's security policies, or a user had not logged in during the window we were monitoring keystrokes, we reset the password. We then forced all users to change their passwords on their next login, and it worked pretty well. It wasn't my decision to use this method, and I'm not sure I'd chose it now: it all depends on the business needs.

At the moment, I'm setting up database mirroring. I need to transfer over the logins from my principal, source server to my mirror server. These are all system accounts, where I know the password (heck, it's in the password safe), but I don't want to create them all manually. Nor do I want to use IDERA's brilliant SQL Permissions tool - I don't need all the user account information - just to script out the logins themselves. After all, the user account information will be contained in the databases that I'm mirroring, once I've restored them to the server. Ideally, I'd like to script them all out in one go. Ideally, I'd like them to have the passwords in the script too.

Fortunately, I can: and without knowing the passwords. Microsoft have created a stored procedure, sp_help_revlogin, that can be used to transfer logins between instances of SQL 2005 and SQL 2008 on different servers (For SQL 2000 and SQL 7.0, check this KB article). It gets created in the master database (yes, I know, goes against all received wisdom about NOT making changes to the master database), along with a second stored procedure, sp_hexadecimal. Executing sp_help_revlogin results in a script that contains all the logins on the server, their default databases, and hexadecimal representations of the binary strings that hold login and password information. Run the script generated by sp_help_revlogin on the destination server, and the logins will be created. You will need to have databases with the correct names on the destination server, which need not be restored versions of the source databases, as the script will not run without the presence of the logins' default databases. They will not have access to connect to any of the databases, as user accounts have not been linked to the logins, but it's easy enough to do that (and there are many scripts available). If you can, it makes sense to restore the databases from the principal server to the mirror server, leaving them in a fully functional state, before running the logins script. And, naturally, if you're not setting up mirroring, then you would do this anyway. With mirroring, you need to make a call: create empty versions of the databases, and restore over them, or restore the databases twice over.

That I could, essentially, copy the logins and passwords across, without having to monitor anything, or know the passwords, was a bit of a revelation. I wish we had known this two years ago.