Changing the Job Owner, doesn’t change the Schedule Owner
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
Posted on May 15, 2018, in Dicas, T-SQL, VirtualPass and tagged Change Job Owner SQL Server, Changing SQL Server Agent Job Ownership, sp_update_schedule, SQL Server Agent Fixed Database Roles, SQLAgentUserRole, The specified @schedule_id does not exist. Bookmark the permalink. Leave a comment.
Leave a comment
Comments 0