Application team requires job execution rights

Question

Application team requires job execution rights

Ryan D.

SSCommitted

Points: 1595
More actions
September 10, 2010 at 3:50 pm

#226806

Hi
I know similar questions has been raised before. Just wondering if anyone has any suggestions or found a best approach.
I have a shared SQL environment running SQL 2005. I recently migrated an application with serveral dozen jobs ranging from TSQL statements to SSIS pacakges. Now my question is what is the best approach to limiting their rights to modify and execute the jobs related to their application but prevent them access to anyone else's jobs.
The easiest fix would be to grant that SQLAgentOperatorRole, this would give them the ability they had in their previous environment. But would open up issues with other groups wanting the access.
Thanks

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

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

Toby White SSCertifiable Points: 6187 More actions · Answer 1

I would not allow them to modify their jobs without the approval process of a DBA. The method you would use for allowing them to modify thier jobs would be to make them owner of thier jobs. However, you would then need to grant sufficient priveleges to thier account and/or to proxies that are assigned to execute certain steps in thier jobs. However, the job steps will likely need so much access as to be a security threat because they could change the job however they please.

I recommend you allow them to execute thier own jobs, but go through the DBA for any creation/changes. Create a stored procedure to execute their jobs that is a wrapper arround msdb.dbo.sp_start_job. Start it like the following:

CREATE Proc sp_start_job_devs

@job_name sysname

WITH EXECUTE AS 'dbo'

In the procedure check for ORIGINAL_LOGIN() to decide whether they can run the job they sent as a parameter based on either a table check or a check against the jobs category. If they can run the job then execute msdb.dbo.sp_start_job with the parameter they sent.

P Jones SSChampion Points: 12353 More actions · Answer 2

Let them have SQLAgentOperatorRole on the development server but not on test or live. DBA does transfer of job to test and then to live once they've set it up and then DBA can check it through.

Don't have a development/test server? Get yourselves and your management on ITIL Foundation course!

Toby White SSCertifiable Points: 6187 More actions · Answer 3

P Jones (9/15/2010)
Let them have SQLAgentOperatorRole on the development server but not on test or live. DBA does transfer of job to test and then to live once they've set it up and then DBA can check it through.

What is needed is for developers to have permissions to run certain jobs in production. Your solution does not address that.

Ryan D. SSCommitted Points: 1595 More actions · Answer 4

We do have a development server, however its shared with serveral other groups. I trying to see what is the best way to grant them the necessary permissions to do their job without accessing or interfering with another group.

THanks,