asked
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
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][1] [1]:
http://msdn.microsoft.com/en-us/library/ms188283.aspx
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?
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.
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.
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 ;)
Add him to the required fixed SQL Agent role and make him owner of the job then ;) That makes sense as well.
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.
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.
+1 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.
8 People are following this question.
