We would like to run a trigger on one database (A) that calls a stored procedure on another database (B).
CREATE TRIGGER trg_A ON TableA FOR INSERT AS BEGIN EXEC DatabaseB.dbo.stp_B END
We would like to do this as LoginA with UserA on DatabaseA. We also have LoginB with UserB on DatabaseB.
How can we accomplish this?
Currently we get this error message
The server principal "..." is not able to access the database "DatabaseB" under the current security context.
We have tried WITH EXECUTE AS 'UserB'
, without luck.
Interesting question. You nerd-sniped me. I tested a bunch of different scenarios.
These failed:
The only way I could get the trigger to work was to create a user in DatabaseB for LoginA and grant permissions to execute the DatabaseB stored procedure.