Running trigger that calls stored procedure on another database

Question

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.

Answer 1

Interesting question. You nerd-sniped me. I tested a bunch of different scenarios.

These failed:

1. Trigger on tableA references stored procedure in DatabaseB. LoginA does not exist as a user in DatabaseB.
2. Trigger on tableA references stored procedure in DatabaseA that inserts into DatabaseB.dbo.TableB. LoginA does not exist in DatabaseB.
3. Created LoginA as a user on DatabaseB, but didn't give it any rights. Repeated test1 and test2. Both failed.
4. Created LoginB as a user on DatabaseA. Made sure LoginB could insert into DatabaseB.dbo.TableB. Logged into DatabaseA as LoginA, ran EXECUTE AS User = 'LoginB'. Tried to insert into DatabaseA.dbo.TableA.

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.