SQL Server Performance Forum – Threads Archive

Creating a Job Manager Role

I am looking for a way to create a role that can administer all SQL Agent jobs from Enterprise Manager, without being SA on the box. I have experimented with the TargetServersRole in msdb, and by granting permissions to start and stop jobs, I get the desired results. However, I would prefer to create my own role and not rely on the TargetServersRole.When I copy the permissions to a role of my own creation, access to jobs fails. There appears to be some "hidden" permission given to the TargetServersRole in msdb. Anyone out there have any luck creating a job manager role and willing to share? Thanks in advance. Terry Duffy

You can let the user have permission on MSDB that can allow to monitor the jobs without being SA on SQL Server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I had the same scenario. I had to allow non sysadmins to be able to view/run specific jobs as well as modify those job’s attributes (schedules,add/modify/delete steps…)
All our jobs are classified under many job categories, but we are diligent in categorizing our jobs.
I created a jobmapping table between Username and JobCategory and had to modify serveral system procs (which I hate doing but I’d rather modify procs than add people to sysadmin) I created a new Role in MSDB and added the user(s) to the role.
I then added logic to the system procs to enter a new code block if the role_member flag = true
Works like a charm.
example of my code: if IS_MEMBER (‘JobManagerGroup’) = 1
BEGIN
IF NOT EXISTS (select Top 1 1 from msdb.dbo.sysjobs sj (nolock)
join msdb.dbo.syscategories sc (nolock)
on sj.category_id = sc.category_id
where sj.job_id = @job_id
and sc.name in
(select JobCategory from msdb.dbo.UserToJobCategoryMap where UserName = SYSTEM_USER)
)
BEGIN
RAISERROR(14260, -1, -1)
RETURN(1) — Failure
END
END The RAISERROR pops up a dialog box in EM with the ‘you do not have permission.." message.
Now non admins can view/run/change existing jobs that they have been given access to.
If you’re interested in the rest of the steps I can write up a little doc.

Thats interesting, why not write an article and submit to Brad. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Sounds good. I’ll work on a document for submission. Thanks
Brian

I appreciate the posts. What I am really trying to find out is how to incorporate the "hidden" permission given to the TargetServersRole, in a role of my own creation. You will find that the TargetServersRole is not even recognized as a role outside of the GUI! Try this code in QA:
GRANT SELECT,UPDATE ON [dbo].[systargetservers] TO TargetServerRole You will receive an error that "There is no such user or group ‘TargetServerRole’" I will not be updating system procedures. Thanks again.
Terry

did you try to reverse engineer how jobs are managed?
you would have found reference to sysjobs_view: CREATE VIEW sysjobs_view
AS
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N’sysadmin’), 0) = 1)
OR (ISNULL(IS_MEMBER(N’TargetServersRole’), 0) = 1)

]]>