Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
2  
Also, Robert L. Davis has a very good blog post on transferring logins between servers, with a simpler code snippet that gets the job done. –  kozloski Jun 13 '12 at 16:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.