Techniques for Allowing Users to Initiate SQL Agent Jobs

Question

Techniques for Allowing Users to Initiate SQL Agent Jobs

Phil Parkin

SSC Guru

Points: 246963
More actions
February 15, 2016 at 1:47 pm

#324331

I'm looking for ideas.
My specific requirement is that I need a decent interface which allows certain users to kick off a SQL Agent job which runs some SSIS packages.
I do not want to GRANT those users the elevated permissions needed to execute sp_Start_Job.
We currently use the run-a-proc-from-SSRS technique to do this, but I'd prefer something that does not feel so much like a hack 🙂
So ... how do you all do this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Create a procedure WITH EXECUTE AS, put the start job in there. User has execute on procedure. Write a small app that just calls the procedure (C#, should be < hour work)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rich Mechaber SSChampion Points: 11130 More actions · Answer 2

Hi, Phil, my memory is that this was rather involved when I implemented this a few years ago. Jonathan Kehayias had a nice write-up about how to do that here: http://www.sqlservercentral.com/articles/Security/68873/[/url] that involves using a certificate to sign a one-line stored procedure that you grant EXEC permissions to whichever users you want to be able to run the Agent job:

EXEC msdb.dbo.sp_start_job N'My Agent Job';

His example was to allow certain users to execute msdb.dbo.sp_send_dbmail .

Though I used this in production for a while, the business ultimately decided that the data extract (which the Agent job had been designed to perform) no longer needed to be run unattended on a schedule. We only needed user-initiated extracts. At that point, I removed all of the extra coding/certificate/agent/SSIS bits and built a small .NET application to run a stored proc (which the department's staff had EXEC permissions for) to fetch and save off the data to a file share.

It's quite possible that I didn't need this level of security and that Gail's suggestion is sufficient. As it was a new implementation to me at the time, I decided to follow Jonathan's recommendation.

Rich

Phil Parkin SSC Guru Points: 246963 More actions · Answer 3

Rich Mechaber (2/16/2016)
Hi, Phil, my memory is that this was rather involved when I implemented this a few years ago. Jonathan Kehayias had a nice write-up about how to do that here: http://www.sqlservercentral.com/articles/Security/68873/[/url] that involves using a certificate to sign a one-line stored procedure that you grant EXEC permissions to whichever users you want to be able to run the Agent job:
EXEC msdb.dbo.sp_start_job N'My Agent Job';
His example was to allow certain users to execute msdb.dbo.sp_send_dbmail .
Though I used this in production for a while, the business ultimately decided that the data extract (which the Agent job had been designed to perform) no longer needed to be run unattended on a schedule. We only needed user-initiated extracts. At that point, I removed all of the extra coding/certificate/agent/SSIS bits and built a small .NET application to run a stored proc (which the department's staff had EXEC permissions for) to fetch and save off the data to a file share.
It's quite possible that I didn't need this level of security and that Gail's suggestion is sufficient. As it was a new implementation to me at the time, I decided to follow Jonathan's recommendation.
Rich

Thank you both. Each of these would get the job done, I am sure.

The solution I am leaning towards right now is to implement this as a job in TeamCity. The TC service account would be granted the necessary DB permissions and user access to the job would be controlled via TC's own security model. I know that this works, as I've done it in the past, but wanted to check the waters for other possibilities.

TC provides controlled access to jobs and has a decent GUI – all I need to do is configure the job steps and the security. No need to worry about the UI, which is a big plus with this idea.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin