We are using SSISDB and SQL agent jobs to run them using SQL Server 2016. My developers have SQLAgentReaderRole, and are able to create their own jobs and schedules. We have a framework for triggering these jobs that uses schedules that are owned by a proxy account. The developers can create their own schedule, but normally they pick one of the framework schedules so that their jobs run automatically after our main etl.

The problem is that if a developer decides they want to change the schedule, and they modify the job through the SSMS gui they get an error when they apply their changes saying Only owner of a job schedule or members of sysadmin role can modify or delete the job schedule

enter image description here

If they script out the change, and execute it they get the same error. enter image description here However if they remove the @delete_unused_schedule=1 (which then passes a 0 as the default per MS's documentation) the command is successful.

This is odd, as all the framework schedules are assigned to at least one job, if not more, and I can see that they are referencing a job by querying sysjobschedulesenter image description here

In regards to the @delete_unused_schedule parameter, Microsoft's documentation states: If set to 1, unused job schedules are deleted if no jobs reference them.

How exactly does it define if the schedule references a job? The schedules are disabled by design, and I have not tested this with a enabled job which may be the cause.

Following up on the first question, is it possible to override the code that SSMS generates and executes when modifying job schedules so that it does not pass the ``@delete_unused_schedule` parameter when people modify their jobs?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.