Execute Permission Denied on Object sp_start_job in SQL Server

sql serversql server 2014sql-server-agent

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified):

ALTER PROCEDURE [dbo].[RunJob]
    @job_name nvarchar(200)
WITH EXECUTE AS 'sysadminaccount'
AS
BEGIN
    --SET NOCOUNT ON;
    BEGIN TRY
        EXEC msdb.dbo.sp_start_job @job_name = @job_name 

        -- Wait for job to finish
        DECLARE @job_history_id AS INT = NULL
        DECLARE @job_result AS INT = NULL

        WHILE 1=1
        BEGIN
            SELECT TOP 1 @job_history_id = activity.job_history_id
            FROM msdb.dbo.sysjobs jobs
            INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
            WHERE jobs.name = @job_name
            ORDER BY activity.start_execution_date DESC

            IF @job_history_id IS NULL
            BEGIN
                WAITFOR DELAY '00:00:01'
                CONTINUE
            END
            ELSE
                BREAK
        END

        -- Check exit code
        SET @job_result = (SELECT history.run_status
        FROM msdb.dbo.sysjobhistory history
        WHERE history.instance_id = @job_history_id)

        RETURN @job_result;

    END TRY
    BEGIN CATCH
        THROW;
        RETURN;
    END CATCH
END

However, when I call this procedure (having verified it is running via "sysadminaccount"), I get the following error message:

Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

The account is a member of the sysadmin role, so I as understand it there shouldn't be any problems kicking off jobs. I've verified that it is a member of the three sqlagent roles in msdb, and those roles all have execute permission on sp_start_job.

How can I give this account the appropriate permissions? Is there something else that needs to be done because of the impersonation?

Best Answer

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.) So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

  1. Create the procedure in msdb.

  2. Create a user for this user's login in msdb:

    USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

  3. Grant the user execute privileges on the stored procedure:

    GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;

  4. Test it - either by calling the procedure from another database:

    USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

    Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

    USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

    Result should be:

    I am really...
---------------
sysadminaccount

  5. Validate that this doesn't expose anything else in msdb to this user:

    USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;

    Result should be...

    Msg 229, Level 14, State 5, Line 21
    The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

    ...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

Related Question