In a previous post (Scheduling Tips and Tricks) I described how to use TSQL to create a schedule and attach it to an existing job.

In this post, I will describe what are the permissions required to create a job/schedule and attach a job to an existing schedule (owned by somebody else).

First, remember that both job and schedule are first class objects with a separate owner.

Here are the quick rules for handling objects:

- Members of sysadmin group can change objects owned by anybody (no restrictions).

- Aside from sysadmins, only members of a Sql Agent Role can perform any object operation (e.g. create/update/delete on jobs or schedules) or any other operation in Sql Agent. There are different levels of permissions for the three Sql Agent Roles.

- Besides the constraint above, there are more permissions checks on any operation on a job or schedule. Except for sysadmins, only a job owner can update or delete a job (and similar for schedule owners).

An interesting scenario is when trying to attach a job to a schedule. The current implementation is:

  • I can attach a job to a schedule only if I own both (again, with the exception of the sysadmin members).
  • I can detach a job from a schedule only if I own both.

There is a possibility that we will relax this last two rules in Sql 11. We would like to allow a job owner to attach to any schedule. Also the same should apply for detach. One nit is that sp_detach_schedule has an optional parameter to delete the schedule if nobody uses it. If selected, the caller needs to own the schedule too (just like today).

This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm