4

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.

1 Answer 1

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
2
  • Interesting. This all comes from an application, so do they need to add the REVERT command after the procedure call? Or can I put that at the end of the wrapper procedure? Commented Jun 25, 2014 at 19:32
  • 1
    No no no, that is just for you to test it out (so you don't have to do so by logging in as them). When they execute it, they'll already have implicitly called the EXECUTE AS and REVERT. Commented Jun 25, 2014 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.