sql server作为触发器中的权限错误执行 [英] sql server execute as permission errors in trigger

问题描述

我有一个触发器，我想在其中发送有关表SalesClosing中一行更新的电子邮件。现在，执行触发器的用户（dbuser）具有非常有限的权限。因此，我希望触发器以dbmailuser的身份执行。有权发送电子邮件的用户。我测试了dbmailuser以该用户身份登录时可以执行sp_send_dbmail。虽然触发了触发器，但出现错误，我无法执行sp_send_dbmail。因此，我以dbuser
身份登录，并以EXECUTE AS LOGIN ='dbmailuser'身份运行；并能够执行sp_send_dbmail。现在，为什么我不能在触发器中执行此操作。我正在使用sql server2008。下面是触发器ddl。

I have a trigger where I want to send out an email on updates to a row in a table SalesClosing. Now the user (dbuser) who execute the trigger has very limited permissions. So I want the trigger to execute as dbmailuser. A user who has rights to send out email. I tested that dbmailuser can execute sp_send_dbmail when logged in as that user. When the trigger fires though, I get an error can't execute sp_send_dbmail. So I logged in as dbuser , ran EXECUTE AS LOGIN = 'dbmailuser'; and was able to execute sp_send_dbmail. Now why can't I do that in a trigger. I'm using sql server 2008. Below is the trigger ddl.

alter TRIGGER SalesClosingTrigger ON SalesClosing
WITH EXECUTE AS 'dbmailuser'
for insert, update
AS
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  --@profile_name = 'Test_Email_Profile',
  @recipients='[e-mail]',
  @body = 'This is a test for Database Mail.',
  @body_format = 'TEXT',
  @subject = 'Database Mail Test'
END 
GO

推荐答案

此是由于按用户执行作用域-上下文切换到数据库级别的用户默认情况下仅作用于该数据库（并且上面的代码在给定数据库之外的msdb中执行），并且仅数据库的身份验证器无效。有关如何解决/解决方法的多种选择，请参见上面的链接。

This is due to execute-as-user scoping - context switching to a database-level user is by default scoped to only that database (and the code above is executing outside the given database across to msdb), and the database-only authenticator is invalid. See the link above for multiple options on how to resolve/work-around.

这篇关于sql server作为触发器中的权限错误执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！