SQL Server 2005 - Map login to pre-existing user

Question

The short of it is that we have several databases that are synchronized between two failover instances. The primary contains logins that are mapped to the users in the databases, and everything's hunky-dory. However, I didn't realize that server logins didn't synchronize between failover instances, so when there is a failover to the backup, nobody can log in. The kicker is that while I can create a login on the backup, I can't map the login to the DBs as a user because a DB user with that name already exists, and the login won't use the existing user, so logins still fail because the login doesn't have rights to connect to the DB.

I think what I have to do is modify the login in the master DB of the backup to have the same GUID as the login from the primary, and manually create the link between the login and user on the backup side, thereby "tricking" the backup instance into thinking the user created on the primary is is its own mapped login as well. However I have no clue how I would go about doing this, and we're having enough trouble with this issue without me flailing at it.

Help?

Answer 1

For a failover-type situation, I use the handy 'sp_help_revlogin' stored procedure as published in http://support.microsoft.com/kb/918992

This procedure, when run on your primary server, will script out each login WITH the SID (this is the important part for making sure your logins are mapped to their respective database users) and hashed password (so the password remains the same on each server.) The resulting script can then be run against your failover server to create the logins. Be sure to review the output before running it.

As-is, it scripts all of the logins in the server, but could be modified to script only the logins you're interested in. It could also be automated to generate a script on a regular basis.