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.

Nothing to do with SQL

For months, now, I have been unable to navigate to the software server from my PC: every time, I'd get a login box, and then an account locked error. Bizarrely, I can RDP to the server just fine, and navigate to the various folders on it if I'm on any other machine. Just not from my PC

In the event viewer on my PC, I found Event 14 (Kerberos)
"There were password errors using the Credential Manager. To remedy, launch the Stored User Names and Passwords control panel applet, and reenter the password for the credential domain\sweetsql"

Well, there's no such applet in the Control Panel, but some googling revealed how to get to it, courtesy of tweekxp.com.

Click on START - RUN and type the following (follwed by ENTER):
rundll32.exe keymgr.dll,KRShowKeyMgr

It seems that Windows XP sometimes stored login credentials in a local cache, and this can be completely out of synch with the rest of the domain or corrupted. Rather than modifying the password in the Stored User Names and Passwords applet, I decided to simply delete the reference to the relevant server: it saves it all going wrong the next time I change my password (and, if I remember correctly, it first started happening when I changed my password).

Although this problem wasn't SQL based, it could affect you when you are using Windows Authentication to connect to a server via SQL Management Studio.

And, now it makes sense...

I had another email from Lightspeed, in response to my request to know why my databases were shrinking even though autoshrink = false, and this got to the root of the matter.

Previous versions of Total Traffic Control could be run on SQL Express, which has a 4GB database size limit: so, TTC has a subroutine that automatically shrinks databases, in order to get round this issue.

Now, however, they don't support running the system on SQL Express, so this code is redundant. However, it's not yet been removed. They're working on that: it'll have to be thoroughly tested first, so it won't be instant.

I'm very glad I asked: and I'm very pleased to have had such a good reply!