It’s quite fascinating that a database can be mirrored across two sites and a front-end application will not have a clue about a failure due to a failover partner string specified in the connection string of the application.
Database mirroring was a new feature introduced in SQL Server 2005 which has seen a sizeable amount of implementation over the years. What people have not noticed is how the application connects to the database on two completely different servers w.r.t. authentication. Most organizations use Windows Authentication and after a database failover, users are able to connect seamlessly. When SQL Authentication is used to connect to the mirrored database after a failover and this login only has logon rights on the mirrored database, then it becomes a different ballgame altogether.
SQL Server stores the information of a login using a SID value. The SID value for Windows Authentications (Domain Accounts) are the same throughout the environment hence a problem never arises here when the database is failed. But for SQL Login. Rule of thumb tells us that the login used to login to a mirrored database should exist on both instances which hosts the principal and mirror databases. So, what happens when you use a SQL Authentication after a failover?? Since, SQL Server relies on SIDs to map a database user to a SQL login. You can find the information under sys.database_users and sys.syslogins catalog views. Since, after a failover the SIDs for a SQL Authentication is bound to have a mismatch (as SQL Authentication SIDs are machine specific and not something derived from the AD as in the case of Domain Accounts), you will start getting login failed or login timeout period expired for SQL Authentication.
To resolve such an issue make sure that the SID of the SQL Authenticated user is the same on both the principal and mirror server instances.
So, if you have run into an issue of having orphaned logins after failing over your mirrored database, then you can follow the steps below to resolve the issue:
1. Use the sp_help_revlogin script to script out the SQL Login
2. Drop the user on the mirrored instance and recreated it using the script obtained above
3. You would have to failover and re-map the SQL login to the database user under user mappings because if you have a database user and drop the SQL login associated with it , then the database user becomes orphaned.
This is documented both in the whitepaper on Database Mirroring on Technet as well as Books Online which talks about resolving orphaned users issue after a database failover.
Related Article:
TroubleshootingSQL 