Execute permission denied on object sp_start_job

Question

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?

Answer 1

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):

• The TRUSTWORTHY Database Property Explained – Part 1
• The TRUSTWORTHY Database Property Explained – Part 2
• The TRUSTWORTHY Database Property Explained – Part 3
• TRUSTWORTHY Database Property
• KB #2183687 : Guidelines for using the TRUSTWORTHY database setting in SQL Server

(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).

Answer 2

Can not comment as i have not enough points so writing answer. Seems like this is cross database ownership chaining issue. If this procedure is created outside of msdb then is has no access to objects in the referenced database, even if "EXECUTE AS" impersonates user as a member of sysadmin role.

So probable solution is:

1. Make your database TRUSWORTHY:

   ALTER DATABASE dbname SET TRUSTWORTHY ON;
   

2. Enable cross-database ownership chaining for the database (may not be necessary)

   ALTER DATABASE dbname SET DB_CHAINING ON;
   

Answer 3

I totally agree with that TRUSTWORTHY is not all that trustworthy of an approach. You should choose another way to get the result that you need.

I recently posted a way to grant the starting of a SQL Agent job to another user or group of users. You can see it at:

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

The third option, a security table and a stored procedure, gives you the most flexibility and the least exposure.

Answer 4

In Sql Server:

just go to security->schema->dbo.. Double click dbo... then click on permission tab->(blue font)view database permission and feel free to scroll for required fields like "execute"....help yourself with choosing....and grant,with grant or deny controls....hope this will help