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?
Best Answer
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:
To reset the mapping between login @login and database user @login.
If this doesn't work, try the 'Auto_fix' approach:
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.