Grant permissions to login for start,stop and modify specific job

Hi, Please suggest grant permission statement to assign start,stop and modify specific job by server login.

SQL Login: sql_loging Server Permission: Public Job Name: job_test Job Owner : Different owner

SQL Version:MS SQL 2005

more ▼

asked Jun 01, 2012 at 11:18 AM in Default

avatar image

1.4k 90 93 96

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

To start,stop or modify a job, a user must be granted one of the following roles in the msdb database:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.

More details on MSDN : SQL Server Agent Fixed Database Roles

more ▼

answered Jun 01, 2012 at 11:24 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Granting all MSDB database roll be give permission on all jobs but I want to give permission on specific job that is owned by other user.

Jun 01, 2012 at 11:33 AM Sharma

I can think of ways around the start/stop, but not for modifying - you either have permission to modify (and create) all jobs (i.e.sysadmin), or your own only.

Why do you want a user to be able to modify a job that's not theirs?

Jun 01, 2012 at 11:52 AM Kev Riley ♦♦

In current setup all server jobs are owned by single domain login that have sysadmin server role permission, but now requirement is to give start,stop and edit permission to other logins also but not to all avaliable job on server that permission should be for specific job.

If I use any fixed MSDB database role then it is not bound with any specific job, that role permission will be for all available job on server.

Jun 01, 2012 at 12:12 PM Sharma
(comments are locked)
10|1200 characters needed characters left

If I look at this scenario

SQL Login: sql_loging Server Permission: Public Job Name: job_test Job Owner : Different owner

And you want to give modify rights as well, then that is only possible with sysadmin.

more ▼

answered Jun 01, 2012 at 11:40 AM

avatar image

Usman Butt
14.7k 6 13 21

Mine requirement to give job modify permission for single job and for that if i will assign sysadmin permision to login then that user would be able to modify all other jobs also. Is ther anything to customize this role for any specific job.

Jun 01, 2012 at 11:45 AM Sharma

Add him to the required fixed SQL Agent role and make him owner of the job then ;) That makes sense as well.

Jun 01, 2012 at 11:47 AM Usman Butt

As per current setup it is not possible to change ownership. For giving fixed SQL Agent role also not for specific job, these also apply all available job on agents in respect of self owned or other user.

Is this could be accomplish by granting executing permission on related MSDB stored procedure to login.

e.g. grant exec sp_update_job or somthing.

Jun 01, 2012 at 11:53 AM Sharma

No. That would not do it. There is a check in that system procedure that would only let sysadmin do the modify stuff. BTW, SQLAgentOperatorRole use this same procedure to enable/disable the job ;)

Jun 01, 2012 at 11:56 AM Usman Butt

Is execuate as stored procedure will work?

Jun 01, 2012 at 12:14 PM Sharma
(comments are locked)
10|1200 characters needed characters left

Depending on what your job is actually doing I would try to look at it in another manner than trying to just focus on getting SQL Server Agent to do what it was not meant to.

Since you could work around the start and stop but not the modify, focus on what they need to modify and why. You might be able to just put all that the job does in a stored procedure, and then give them permissions to alter that procedure. Then with a work around to start/stop the job, you would have your solution. Again though that would completely depend on what the job is actually doing.

more ▼

answered Jun 01, 2012 at 12:49 PM

avatar image

6.6k 21 26 34

  • It has no sense to workaround the modify permission. In case the job is being executed as sysadmin then anybody with modify permission could modify the job to execute whatever code on the server. Including granting sysadmin permisison to himself or execute any maulicious code.

The proper way, is correct setup of proxy accounts acompanied with proper MSDB roles membership related to the Agent. Then Owners of corresponding jobs can modify them and use proxy accounts necessary for operation needed.

Jun 01, 2012 at 01:03 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 01, 2012 at 11:18 AM

Seen: 9695 times

Last Updated: Jun 01, 2012 at 01:03 PM

Copyright 2018 Redgate Software. Privacy Policy