Disconection of Database users in mirror configuration

On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.

On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.

Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.

After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.

Hi,

I quoted the topic "Managing server metadata and jobs after role switching" in BOL.

"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "

Peng