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

image

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.

image

As we see, job schedules also have owners.

I hope you enjoyed this quick article.

Marcos Freccia
SQL Server DBA
Data Platform MVP

About Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Posted on May 15, 2018, in Dicas, T-SQL, VirtualPass and tagged , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: