SQL Server 2008插入触发器未触发

Question

我在一个表上有一个 INSERT 触发器，它只是执行一个作业。

示例：

CREATE TABLE test
(
    RunDate smalldatetime
)

CREATE TRIGGER StartJob ON test 
AFTER INSERT 
AS
    EXEC msdb.dbo.sp_start_job 'TestJob'

当我向该表插入一条记录时，该作业将被解雇，没有任何问题。然而，有一些人的权限比我低（仅限数据库上的 db_datareader/db_datawriter）；他们能够向表中插入一条记录，但触发器不会触发。

我是一名 SQL Server 新手，我的印象是用户不需要提升权限来触发触发器（我认为这是最大的好处之一！）。这是触发器级别的权限问题，还是工作级别的权限问题？我可以做什么来绕过这个限制？

Answer 1

触发器将在调用者的上下文中执行，调用者可能有也可能没有访问 msdb 的权限。这似乎是你的问题。有几种方法可以使用“执行为”来扩展这些权限；它们在此链接中有详细说明

在触发器中使用模拟：

CREATE TRIGGER StartJob ON test  
with execute as owner
AFTER INSERT 
AS
EXEC msdb.dbo.sp_start_job 'TestJob'

并设置数据库值得信赖（或阅读有关登录上面的链接）：

alter database TestDB set trustworthy on

另一种方法（取决于代理作业执行的操作）是利用 Service Broker 队列来处理存储过程激活。您的用户上下文将简单地调用发送队列，而在异步过程中，SvcBroker 将激活在更高权限的用户上下文中执行的存储过程。我会选择此解决方案，而不是依赖于调用代理作业的触发器。

我想测试对 Service Broker 的调用，所以我编写了这个简单的测试示例。我只是发送电子邮件，而不是调用 SSIS 包，但这与您的情况非常相似。请注意，我在脚本顶部使用了 SET TRUSTWORTHY ON。请阅读此设置的含义。

要运行此示例，您需要替换下面的电子邮件个人资料信息， 等。

use Master;
go
if exists(select * from sys.databases where name = 'TestDB')
    drop database TestDB;
create database TestDB;
go
alter database TestDB set ENABLE_BROKER; 
go
alter database TestDB set TRUSTWORTHY ON;

use TestDB;
go

------------------------------------------------------------------------------------
-- create procedure that will be called by svc broker
------------------------------------------------------------------------------------
create procedure dbo.usp_SSISCaller
as
set nocount on;
declare @dlgid uniqueidentifier;
begin try

    -- * figure out how to start SSIS package from here

    -- for now, just send an email to illustrate the async callback 
        ;receive top(1) 
                @dlgid = conversation_handle
        from SSISCallerQueue;

        if @@rowcount = 0
        begin
            return;
        end

        end conversation @dlgid;

    exec msdb.dbo.sp_send_dbmail 
        @profile_name           = '<your_profile_here>',
        @importance             = 'NORMAL',
        @sensitivity            = 'NORMAL',
        @recipients             = '<your_email_address_here>', 
        @copy_recipients        = '',
        @blind_copy_recipients  = '', 
        @subject                = 'test from ssis caller',
        @body                   = 'testing',
        @body_format            = 'TEXT'; 

    return 0;

end try
begin catch
    declare @msg varchar(max);
    select @msg = error_message();
    raiserror(@msg, 16, 1);

    return -1;
end catch;
go

------------------------------------------------------------------------------------
-- setup svcbroker objects
------------------------------------------------------------------------------------
create contract [//SSISCallerContract]
    ([http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer] sent by initiator)

create queue SSISCallerQueue 
    with status = on, 
    activation (    
        procedure_name = usp_SSISCaller,
        max_queue_readers = 1,
        execute as 'dbo' );

create service [//SSISCallerService] 
    authorization dbo
    on queue SSISCallerQueue ([//SSISCallerContract]);
go

return;


-- usage 
/*

-- put a row into the queue to trigger the call to usp_SSISCaller

begin transaction;

    declare @dlgId uniqueidentifier;

    begin dialog conversation @dlgId
                from service   [//SSISCallerService]
                to service      '//SSISCallerService', 
                                'CURRENT DATABASE'
                on contract     [//SSISCallerContract]
                with encryption = off;

    begin conversation timer (@dlgId)
            TIMEOUT = 5; -- seconds

commit transaction;
*/

Answer 2

这将是工作级别的权限。您可以在 MSDB 中为这些用户分配 SQLAgentReaderRole 以便能够启动作业，考虑到他们将被添加到拥有该作业的组中。如果他们不属于拥有这份工作的团体，事情就会变得更加困难。