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
asked Jun 01 '12 at 11:18 AM in Default
To start,stop or modify a job, a user must be granted one of the following roles in the msdb database:
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
answered Jun 01 '12 at 11:24 AM
Kev Riley ♦♦
If I look at this scenario
And you want to give modify rights as well, then that is only possible with sysadmin.
answered Jun 01 '12 at 11:40 AM
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.
answered Jun 01 '12 at 12:49 PM