1

I have a trigger that accesses another database. but I receive an error:

"The server principal 'A' is not able to access the database 'B' under the current security context"

Where A is is User login. I then set it to execute as a user which with the same login in both databases (the database from which the trigger is called and the database referenced in the trigger). However, this error persist. I set the referened database to thrustworthy but that did not work.

Also, sa is not a user but a login but when I run the trigger is issued from an sa login without any impersonation, all works well; but all breaks when i try impersonating any user.

How do I have this trigger to run for all users including those not having access to my referenced database

jpo
  • 123

1 Answers1

1

You would have to grant access to public to all items in the referenced database that the trigger is set to reference, which is a bad idea.

Otherwise you will need to grant access to your referenced database and any of its objects (tables, views, stored procedures, etc) to any login that will need to use the trigger.

Alternatively, if your trigger is getting fired from an application call you can create a login for the applicaiton, configure the permissions appropriately in both databases for that login, and set the app to connect as that user. You would then, of course, need to take care of other security and logging concerns at the application level.

squillman
  • 38,013
  • Thank you, I tried your second option and strangely, it does not work. I grant access to the referenced database to the user with login "test" and I omit "Execute as "test", all works well when this user initiates the trigger. But it does not work when I include "Execute as "test"" through user still has access. Any inputs on this? – jpo Sep 26 '14 at 16:20
  • Sorry, see my edit. You need to grant access to the underlying db objects within the referenced database also. Just granting access to the database is not enough. – squillman Sep 26 '14 at 16:28