I ran into a problem today with permissions and jobs. As I’ve mentioned previously, I’m in the middle of auditing access rights on our SQL Servers and removing unnecessary sa rights from logins that don’t need them.

In our shop, we have developers who build SSIS package and schedule them to run on our production servers. Since I took away sa rights from them, they have not been able to edit some of those jobs. So I added them to the SQLAgentOperatorRole in msdb. According to this BOL entry, this is the most privileged role of the SQLAgent roles in msdb.

Turns out, that doesn’t quite do what I want. Members of this role can edit jobs they own, but not jobs they don’t own. This doesn’t really work in our situation. A bit of Googling shows this seems to be a common complaint. The only way to allow a user to edit jobs owned by anyone is to grant them sa rights or make them the job owner. In fact, if you look at the code of the system stored procedure sp_update_jobstep, you see the following check:

  -- 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

(This is in both 2005 and 2008 R2. I haven’t checked other versions.)
Hmm. I obviously don’t want to give people sa rights. What I ended up doing was changing the job owner to the person who wanted to edit the job. This may have some implications for jobs that call xp_cmdshell or don’t set the Run As: property on their job steps, but in my case, the jobs launch SSIS packages, which use their own connection credentials, so that doesn’t affect me.