SA permissions issues with many nested objects 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:


sa is 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?

Updates

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).
Can tell you solution I came up with in situation like yours (its quite complicated, but still love it):

I have database DB1 with Service Broker an DB2 where data has to be updated as well (I do have triggers in DB2 as well).

I am using certificate to create server login + users in each db. I am signing procedures and triggers to Grant rights. You can read everything you need here.

-- Master DB:
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Example'
Go
BACKUP CERTIFICATE MyCertificate TO FILE = 'c:\Cert.cer' 
    WITH PRIVATE KEY ( 
    FILE = 'c:\CertKey.key', 
    ENCRYPTION BY PASSWORD = 'pwd');
Go
Create Login CertLogin From Certificate MyCertificate

-- DB1:
CREATE CERTIFICATE MyCertificate
FROM FILE = N'c:\Cert.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\CertKey.key',
        DECRYPTION BY PASSWORD = N'pwd'
    ); 

Create User CertUser From Login CertLogin;

-- DB2:
CREATE CERTIFICATE MyCertificate
FROM FILE = N'c:\Cert.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\CertKey.key',
        DECRYPTION BY PASSWORD = N'pwd'
    ); 
Go
CREATE USER [CertUser] FROM Login CertLogin;


To grant rights (In my case no need for Select/Insert/Update/Delete rights because all tables and procedures in DB2 is in the same schema. So ownership chaining in DB2): 

-- DB1:
Grant Exec On dbo.usp_MyProc_DB1 To SomeUser -- User that calls procedure
ADD SIGNATURE TO dbo.usp_MyProc_DB1 BY CERTIFICATE MyCertificate;
-- DB2:
ADD COUNTER SIGNATURE TO dbo.usp_MyProc_DB2 BY CERTIFICATE MyCertificate; 
ADD COUNTER SIGNATURE TO tr_MyTrigger_DB2 BY CERTIFICATE MyCertificate;
Grant Exec On usp_CheckPatientDetailedRole To CertUser;