Giving users rights to execute SQL Agent Jobs

Question

Giving users rights to execute SQL Agent Jobs

Gordon Radley

SSCommitted

Points: 1776
More actions
April 6, 2004 at 9:06 am

#108114

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!

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

There is an undocumented role within msdb, TargetServersRole, which I believe has permission to do this. It would require granting individual users access to msdb and then making them members of this role.

K. Brian Kelley
@kbriankelley

jcook119 Old Hand Points: 379 More actions · Answer 2

I have used the TargetServersRole successfully to allow this behavior, but be warned that SQL 2000 SP3a removes several permissions from the role that you will need to manually re-grant. There are a couple of other ways that it can be done, but they involve modifying system stored procedures. Obviously, that is not recommended

Thanks,

James

EdVassie SSC Guru Points: 60469 More actions · Answer 3

As suggested, you can use TargetServersRole, but with modified permissions. This will allow users access to all jobs. If you give them the ability to add/change/delete jobs, this will apply to ALL jobs.

There is no way with Enterprise Manager to give users the ability to run just their own jobs. If you need this level of control you must buy/write a job management application. I believe that Yukon does have this finer granularity on job security, so a fix is on its way...

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Gordon Radley SSCommitted Points: 1776 More actions · Answer 4

Thank you all for the feedback.

I implemented this process and it works great. The only permissions that I needed to add back so far are sp_startjob and sp_stopjob.

Thanks again

Li Lian Newbie Points: 7 More actions · Answer 5

Hello.

I've got one issue.

I've tested setting the TargetServerRole, the individual users can create job and job step now, but in the database list, the user can't see it's own database. Can only see master, tempdb, Northwind, pubs, model, msdb.

Help....

-elcube