SQL Login keeps losing access to databases
Short answer:

The SIDs for the ‘MyReportAcct’ SQL login are different on each server instance.

Long answer:  

The master database houses the security database for each instance of SQL Server (think Active Directory for a database server).  In this scenario we have two different master databases, since it is a mirrored pair.  Each time you create a SQL Login, there is a different SID created along with the account, this is no different than how Active Directory has always worked.

When a mirrored database fails over, the SID in the database matches the SID stored in the master database of the principal server (SERVER1), not the mirrored server (SERVER2).  This natural, and default process of failover causes the SQL Login (MyReportAcct) to become an orphaned account on the principal server (SERVER1).  This orphanhood process happens EVERY time there is a failover.  The start of authority for SQL Logins are the instance that they were created (SERVER1).  The failover to the mirrored server (SERVER2) is now a different start of authority, so the SIDs will be different, hence why the reports fail.

Solution:


Create the SQL Login ‘MyReportAcct’ with the same SID every time. 
This process requires using TSQL code in the query analyzer by
someone who is a sysadmin.
Create an Active Directory service account named ‘uic\MyReportAcct’. 
This way, the SID is managed by AD and we never have to worry about
the aforementioned issue again.