1

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?

6
  • Most likely, this error occurs due to a failover from one node to another. Compare sys.server_principals between AG instances. For each login, is the sid the same across nodes? – AMtwo Feb 6 '17 at 11:38
  • Thanks AMTwo, I am attempting to change the preferred instance on one database, and then testing it at that point, will keep posted, thank you – Peter PitLock Feb 6 '17 at 12:52
  • Chasing the preferred backup replica isn't the answer. Are both databases part of the same AG? If not, why not? If they are in the same AG, then changes to the Operations and ChangeHistory databases will always happen together, on the same instance. – AMtwo Feb 6 '17 at 13:09
  • Are these databases in two different availability groups?! – Sean Gallardy - Retired User Feb 6 '17 at 14:56
  • Both databases are on the same AG. I did want to know whether its possible for the 2 databases on one SQL instance to have different preferred instances - but it sounds like this will never be the case? – Peter PitLock Feb 7 '17 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.