I have a broker application that's relatively complicated.
Today, after I made some changes, I started getting the error:
The server principal 'sa' is not able to access the database 'XYZ' under the current security context.
The whole scenario up to the point of the error is:
(In Database ABC)
- A broker message gets submitted into a queue
- The queue reader picks it up
- The queue reader updates a table to indicate the work is starting
- This table has an update trigger on it. The trigger
- Checks a table in database XYZ to make sure the inserted value for this field is valid
- The table is accessed via a synonym
The check in the trigger I believe is what is causing the issue.
If I run the update manually, it works fine. I have also used
EXECUTE AS to run the update manually as
sa which works fine.
Other relevant facts:
sais the owner of both database ABC and database XYZ
- there's no funny business with the sa account - it's db_owner role in both DBs as well
Is there some sort of strange scoping happening because all this is running in the context of broker?
Some more info:
- DB ownership chaining is on at the server level but not in the DBs. I turned it on and it made no difference.
- Using a three part name instead of a synonym didn't make a difference
- Profiler trace shows that the statements are running as SA and not another login
- Setting both databases TRUSTWORTHY to ON didn't make a difference
- If I run the queue activation proc manually, it processes correctly (under my credentials).