SQL Server 2008 insert trigger not firing

Question

I have an INSERT trigger on a table that simply executes a job.

Example:

CREATE TABLE test
(
    RunDate smalldatetime
)

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

When I insert a record to this table, the job is fired of without any issue. There are a few people, however, that have lower permissions than I do (db_datareader/db_datawriter on the database only); they are able to insert a record to the table, but the trigger does not fire.

I am a SQL Server novice and I was under the impression that users did not need elevated permissions to fire off a trigger (I thought that was one of the big benefits!). Is this a permission issue at the trigger level, or at the job level? What can I do to get around this limitation?

Answer 1

The trigger will execute in the context of the caller, which may or may not have the permissions to access msdb. That seems to be your problem. There are a few ways to extend these permissions using Execute As; they are greatly detailed in this link

Use impersonation within trigger:

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

And set database to trustworthy (or read about signing in above link):

alter database TestDB set trustworthy on

Another way to go (depending on what operations the agent job performs) would be to leverage a Service Broker queue to handle the stored procedure activation. Your users' context would simply call to Send On the queue while, in an asynchronous process SvcBroker would activate a stored procedure which executed in context of higher elevated user. I would opt for this solution rather than relying on a trigger calling an agent job.

I wanted to test the call to Service Broker, so I wrote this simple test example. Instead of calling an SSIS package I simply send an email, but it is very similar to your situation. Notice I use SET TRUSTWORTHY ON at the top of the script. Please read about the implications of this setting.

To run this sample you will need to substitute your email profile info below, <your_email_address_here>, etc.

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;
*/