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
If they script out the change, and execute it they get the same error.
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.
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?