Giving Users Access to execute SQL Agent jobs

[gradley]

I am trying to give individual users the ability to execute SQL Agent jobs from within Enterprise Manger without granting them Sysadmin rights. I thought processadmin would do the trick but it doesn't.

Does anyone have another resolution to this problem.

Thanks in advance!

 

[Corran007]

You can create a role and add all the stored procedures required to do things for sqlagent.

This script should work for you. Thanks mr Knight.

Use MSDB
If (select count(*) from sysusers where name = 'JobUsers') = 0
Begin
exec sp_Addrole 'JobUsers'
Print 'Adding JobUsers Role
End

Go

Grant Execute on sp_help_job to Jobusers
Grant Execute on sp_help_jobstep to Jobusers
Grant Execute on sp_help_jobschedule to Jobusers
Grant Execute on sp_add_jobserver to Jobusers
Grant Execute on sp_add_job to Jobusers
Grant Execute on sp_update_job to Jobusers
Grant Execute on sp_ad_jobstepto Jobusers
Grant Execute on sp_delete_job to Jobusers
Grant Execute on sp_purge_jobhistory to Jobusers
Grant Execute on sp_help_jobhistory to Jobusers
Grant Execute on sp_delete_jobserver to Jobusers
Grant Execute on sp_help_jobserver to Jobusers
Grant Execute on sp_get_jobstep_db_username to Jobusers
Grant Execute on sp_update_jobstep to Jobusers
Grant Execute on sp_delete_jobstep to Jobusers
Grant Execute on sp_add_jobschedule to Jobusers
Grant Execute on sp_update_jobschedule to Jobusers
Grant Execute on sp_delete_jobschedule to Jobusers
Grant Execute on sp_get_job_alerts to Jobusers
Grant Execute on sp_start_job to Jobusers
Grant Execute on sp_stop_job to Jobusers
Grant Execute on sp_check_for_owned_jobs to Jobusers
Grant Execute on sp_check_for_owned_jobsteps to Jobusers
Print 'Grant Complete'

If there is anything in there you dont need, just remove it.

 

[gradley]

Thanks for the reply.

I ran the above script and it created the new role in MSDB as expected, along with the grant rights.

I assume the next step would be to assign the appropriate users to this database and role? I have attempted this but I'm still not able to see any of the jobs in SQL Server Agent when signed on as one of these users.

Do I need to have the users execute the sp_Start_Job to run agent jobs or is there something else I'm missing here.

Thanks again for you help!

 

[Corran007]

Not sure exactly.

this worked for me so im not sure.

To test, i created a new user and gave them privlages to msdb and that role. i then connected via em as that user and went into the jobs and created a new one. It showed up fine.

Are you able to see the job when logged in a sysadmin?

 

[gradley]

I was able to find another workaround similar to method you mentioned. Appearently there is a TargetServersRole in the msdb that allows similar funcitonality and you only need to add sp_start_job and sp_stop_job permissions. Using this role also allows you to see all SQL Agent Jobs and not just those the user created.

Thank you for your feedback!