We recently had some issues with the failover on a mirrored database: the login kept failing when the primary was down.
On connecting to the database in question with the the login that was failing (in order to check the password) I discovered the reason why. The failover database appeared to be stuck in the "restoring..." state rather than "mirror/synchronised / restoring" as expected.
However I found this slightly peculiar because the primary server was reporting that mirroring was working correctly. On logging in to the failover DB using an administrative account, it's status reported correctly as "mirror/synchronised / restoring".
I've never seen this before - that different accounts looking at the same database can report a different mirroring status. The lower-level access account we usually use behaves in all respects as though the failover DB is stuck in restoring - when you attempt to change permissions, for example, it reports that one of the DB's is inaccessible.
What's caused this curious state of affairs, and how can I fix it?
Cheers, Matt
