One of the capabilities that we do offer to our developers is the ability for themselves to create SQL Server Agent Jobs in our DEV/TEST Environments, so they perform some of their work without the DBA to be involved.

To fullfil this requirement, we grant them the role SQLAgentUserRole, which pretty much does the job. They can create and schedule jobs and execute them. However, latelly one of our DEV’s changed his team and I had to transfer his SQL Agent Jobs to another DEV.

It turns out that when the DEV tried to edit the existing schedule he got the following message: The specified @schedule_id (‘ScheduleIDHere’) does not exist.

Doing some investigation around, I found this table in the msdb database: dbo.sysschedules, on which doing a query could be return the following result.

SELECT name,SUSER_SNAME(owner_sid) AS ScheduleOwner,enabled FROM dbo.sysschedules

As you can see, not only a job has an owner, but the schedule itself has an owner too. Now it comes the trick question. How do I change the schedule owner? Well, that’s pretty simple!

In the msdb database, just use the below query.

EXEC dbo.sp_update_schedule @name = ‘MyScheduleNameHere’,
@enabled = 1,
@owner_login_name = ‘NewOwnerLogin’;

Another lesson learned for me in this case was going back to the documentation: SQL Server Agent Fixed Database Roles and see this important in the Role description.

As we see, job schedules also have owners.

I hope you enjoyed this quick article.

Marcos Freccia
SQL Server DBA
Data Platform MVP