2

I have two Microsoft SQL Server instances set up for mirroring each with a number of databases. There are a number of logins and for each database one or more user/login mappings.

When I restore a backup of database I always have to redo the login/user mappings. I understand this because the logins are per database server. So after restoring the databases on the pricipal I redid the login/user mappings. This was not possible for the mirror because the databases were 'restoring'.

After a manual failover I could not use the databases because user credentials were missing. This was not unexpected, so I did the login/user mapping again.

I did a manual failover again to make the initial pricipal, which was now the mirror, principal again. To my surprise I could not use the databases because the login/user mappings were gone.

Is this the expected behaviour?

Improve this question

3 Answers 3

Reset to default
2

Are these SQL logins? If so, then chances are that the mapping between SQL logins and database users is out-of-sync between the principal and mirror.

I've had issues with this myself in the past though I've been lucky enough that I only ever cared about one specific user.

You should try the following function on the mirror:

EXEC sp_change_users_login 'update_one', @login, @login

To reset the mapping between login @login and database user @login.

If this doesn't work, try the 'Auto_fix' approach:

EXEC sp_change_users_login 'Auto_Fix', @login, NULL, @password

This should create the database user and map it accordingly if it doesn't already exist.

This happens with SQL logins because the the database server will generate SIDs for these logins. These SIDs are what tie a database user to a login. Even though the same login may exist on two different servers, their SIDs may be different, and thus you experience "lost" credentials, if you will. This does not happen for windows accounts, because SQL Server will use the Windows SID of the account itself when creating the associated login.

Improve this answer
1

Running sp_change_users_login is fine as a work-around, but I like to fix this (common) problem with the following steps:

  1. Identify the SQL Server logins that you are having trouble with. You should be able to ignore domain logins. It would be best to ignore any SQL Server logins that you app does not use, the best example being "sa".

  2. Choose a server that will be your "known good" system. The current primary is probably the best choice.

  3. Use sp_help_revlogin to reverse engineer (from the primary) a TSQL script to create the problematic logins. sp_help_revlogin is maintained by Microsoft at this page. When run on the secondary, the TSQL script will preserve the "SID" that SQL Server uses to identify logins and link them to the users in each database.

  4. On the secondary, drop the problematic login and run the TSQL script you generated. When the login is recreated, it's SID will match that of the primary server. The next time you fail over or restore a database from the primary to the secondary, the SIDs should match and you should not have to run the sp_change_users_login procedure to 'fix things'.

Until you are familiar with this process, it's best to do one login at a time.

Be wary: Running that TSQL script will also sync the password on the secondary with that of the primary. If you have different passwords on the two servers (maybe one is a development server and one is a production server), you may have to go back in change the password on the secondary back to whatever it is supposed to be. If you don't know what those passwords are, or can't get someone to tell you, this can be a problem.

  1. After running the script on the secondary, you should double check any databases on the secondary that are not part of your app and fix the user-to-login mapping in those. For example, perhaps you have AdventureWorks on that system. When you drop the login, the user-to-login mapping will be broken and will have to be recreated.
Improve this answer
1

On my DBs I usually use these commands:

EXEC sp_change_users_login 'Report'
EXEC sp_change_users_login 'Auto_Fix', 'username'
EXEC sp_change_users_login 'Report'

where username is the user name you want to fix.

Improve this answer

You must log in to answer this question.