在另一个数据库上运行调用存储过程的触发器 [英] Running trigger that calls stored procedure on another database

问题描述

我们想在一个数据库 (A) 上运行触发器，该触发器调用另一个数据库 (B) 上的存储过程.

<前>在 TableA 上创建触发器 trg_A插入作为开始EXEC 数据库B.dbo.stp_B结束

我们希望以 LoginA 的身份与 DatabaseA 上的 UserA 执行此操作.我们在 DatabaseB 上也有 LoginB 和 UserB.

我们如何才能做到这一点?

目前我们收到此错误消息

服务器主体..."无法访问当前安全上下文下的数据库DatabaseB".

<小时>

我们尝试了 WITH EXECUTE AS 'UserB'，但没有成功.

解决方案

有趣的问题.你书呆子狙击了我.我测试了很多不同的场景.

这些失败了:

1. 触发 tableA 引用 DatabaseB 中的存储过程.LoginA 不作为 DatabaseB 中的用户存在.
2. 触发 tableA 引用 DatabaseA 中插入到 DatabaseB.dbo.TableB 中的存储过程.登录 A 在数据库 B 中不存在.
3. 以 DatabaseB 上的用户身份创建 LoginA，但未授予它任何权限.重复 test1 和 test2.都失败了.
4. 以 DatabaseA 上的用户身份创建 LoginB.确保 LoginB 可以插入到 DatabaseB.dbo.TableB 中.以 LoginA 身份登录到 DatabaseA，运行 EXECUTE AS User = 'LoginB'.试图插入到 DatabaseA.dbo.TableA.

让触发器工作的唯一方法是在 DatabaseB 中为 LoginA 创建一个用户并授予执行 DatabaseB 存储过程的权限.

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:

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.

这篇关于在另一个数据库上运行调用存储过程的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！