Post reply

Problems creating SQL Agent Job from within a stored procedure

  • March 13, 2014 at 6:45 am

    #289244

    Hi, I've been battling with this for some time now and can't see what I'm missing.

    I have a windows user who is the dbo for a database.

    I'm trying to create a SQL Agent Job with that user as the owner.

    They are a member of the SQLAgentUserRole

    If I run the following SQL I can create the Job

    declare @Owner varchar(100)

    declare @JobID uniqueidentifier

    EXEC msdb.dbo.sp_add_job @job_name = 'TestJob', @enabled = 1,

    @notify_level_eventlog = 2, -- When the job fails

    @Owner_login_name = @Owner,

    @job_id = @JobId OUTPUT

    select @JobID

    If however I put that code in a sored procedure to execute as the owner and run it

    alter procedure test with Execute as Owner

    as

    declare @Owner varchar(100)

    declare @JobID uniqueidentifier

    EXEC msdb.dbo.sp_add_job @job_name = 'TestJobstoredProc', @enabled = 1,

    @notify_level_eventlog = 2, -- When the job fails

    @Owner_login_name = @Owner,

    @job_id = @JobId OUTPUT

    select @JobID

    go

    execute test

    I get an error

    Msg 229, Level 14, State 5, Procedure sp_add_job, Line 1

    The EXECUTE permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'.

    What am I missing?

    Please help

  • March 13, 2014 at 9:12 am

    #1696840

    OK worked it out, though not sure of exact reasoning behind it.

    I found this statement in MS help for the Execute As User statement

    While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers.

    So removing the 'with execute as owner' from the stored procedure made it work

    alter procedure test --with Execute as Owner

    as

    declare @Owner varchar(100)

    declare @JobID uniqueidentifier

    EXEC msdb.dbo.sp_add_job @job_name = 'TestJobstoredProc', @enabled = 1,

    @notify_level_eventlog = 2, -- When the job fails

    @Owner_login_name = @Owner,

    @job_id = @JobId OUTPUT

    select @JobID

    go

    execute test

    go

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply