SA permissions issues with many nested objects

Question

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).

Answer 1

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;