SQL Agent Permissions - Job Level

Question

We currently have an SQL Job that runs an SSIS package. The output of the package is required for a report. As a result, the application that can create the report on demand first tries to run the job that executes the package. Because this is run by various people at various times, the previous way to handle this was to give SQLAgentOperatorRole to whichever person was in charge of kicking off the report. I really would rather not be handing out this role (and I was not the one responsible for setting the precedent), for obvious reasons.

From what I know, you cannot give job level permissions to any group of people. I have seen things about proxies, but not sure that would help here. So, how do I grant X users access to:

• check job status (via sp_help_job, at the moment)
• Execute this job, and only this job

We are running SQL2008R2.

Answer 1

First, make sure you have a user in msdb associated with their login.

USE msdb;
GO
CREATE USER username FROM LOGIN loginname;

Next, deny execute on sp_help_job and sp_start_job for that user.

USE msdb;
GO
DENY EXECUTE ON dbo.sp_help_job TO [username];
DENY EXECUTE ON dbo.sp_start_job TO [username];

Next, create two wrapper stored procedures that execute as owner and call the above stored procedures with that hard-coded job_id. Here is the start version, the help version will look amazingly similar.

USE msdb;
GO
CREATE PROCEDURE dbo.start_that_job
WITH EXECUTE AS OWNER
AS 
BEGIN
  SET NOCOUNT ON;

  EXEC dbo.sp_start_job @job_id = 'ACE1...';
END
GO
GRANT EXECUTE ON dbo.start_that_job TO [username];
GO

Now, they'll have to call that stored procedure directly, instead of using the UI.

Test it out:

EXECUTE AS USER = N'username';
GO
EXEC dbo.sp_start_job @job_id = 'ACE1...'; -- fails
GO
EXEC dbo.start_that_job; -- succeeds
GO
REVERT; -- this is important