Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).
share|improve this question
    
Same error with a 3pn! –  JNK May 16 '13 at 15:55
    
DB ownership chaining is not on for either DB. –  JNK May 16 '13 at 15:56
    
I didn't think chaining should matter if the relevant account is the owner of both DBs –  JNK May 16 '13 at 15:57
    
Would Broker being disabled in XYZ matter at all? –  JNK May 16 '13 at 15:59
    
DB Chaining didn't make a difference –  JNK May 16 '13 at 16:02
add comment

1 Answer

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.