We are experiencing this error intermittently:
The server principal is not able to access the database under the current security context
The database is part of an Availability Group.
Context : A trigger is fired on "Operations" database, which records entry in ChangeHistory database whenever a record is changed in Operations.
We literally have thousands of records working every minute with no issues. It happens so intermittently that it is very difficult to pinpoint. E.g. it could happen for a 20 minute period now - and then again only in 2 day's time and for only 4 minutes.
I believe that although the error message is what it is, that there is a different issue giving the same error message.
I wonder if it could be that the sys.fn_hadr_backup_is_preferred_replica for
Operations and ChangeHistory are out of sync?
E.g.
DBInstance1 Operations is_preferred_replica=1,ChangeHistory preferred_replica=0.
DBInstance2 Operations is_preferred_replica=0,ChangeHistory preferred_replica=1.
The DBInstance1 which is active , would then try to write to its own ChangeHistory database, which is now not the preferred instance and would generate some sort of error - even though it does not match.
Would this be possible - different DBs with different preferred replica statuses on one server?
If it is, would this be the error that is displayed?
