Grant rights to edit all jobs in SQL Server 2005

[JZajda]

How do I grant rights to edit all jobs in SQL server 2005? There are roles to edit only jobs ownded by the user, and ones that can view/stop/start etc..., but I don't see one that will let a user updated them.

Thanks,
Joy

 

[mrdenny]

Here is the logic used in the sp_update_job procedure.

IF ( (@x_owner_sid <> SUSER_SID())                  -- does not own the job
      AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)   -- is not sysadmin
      AND (@enable_only_used <> 1 OR ISNULL(IS_MEMBER(N'SQLAgentOperatorRole'), 0) <> 1))

The first check looks to see if the user owns the job.
The second check looks to see if the user is a sysadmin.
The third one looks to see if the @enable parameter is the only parameter which was passed besides the jobname or jobid or the user is not a SQLAgentOperatorRole. If all of those fail the the procedure exists.

In other words being a member of the SQLAgentOperatorRole "should" grant the user enough rights to modify the SQL Agent Jobs.

However when the user does this it will reset the owner of the job to that user.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JZajda]

Thanks for the point in the right direction. I checked out the code in the sp_update_job_step and see the following...

-- Check permissions beyond what's checked by the sysjobs_view
-- SQLAgentReader and SQLAgentOperator roles that can see all jobs
-- cannot modify jobs they do not own
IF (@job_owner_sid <> SUSER_SID() -- does not own the job
AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)) -- is not sysadmin
BEGIN
RAISERROR(14525, -1, -1);
RETURN(1) -- Failure
END

It looks like you either need to own the job or be a server sysadmin. I was hoping for some other alternative.

Thanks.
Joy

 

[mrdenny]

Is the server SQL 2005 or SQL 2000? And what service pack?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JZajda]

2005 - SP2. 9.00.3042.00