SQL Server Job Ownership Change

The purpose of this blog is to check the current owner of a job and modify the owner to SA or whatever user is preferred in your company. Here, we will learn to display the owner of the jobs, learn to generate a script that changes the owner of all the jobs in a db Instance.

How to list the job and their owner?
SELECT NAME, 
       Suser_sname(owner_sid) AS owner 
FROM   msdb.dbo.sysjobs 
ORDER  BY owner; 

How to change the ownership of a job?
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Your Job Name', 
@owner_login_name=N'DOMAIN\ThenewOwner'
GO

The above SQL lets you change the owner of a job when you have a single job to change. How would you proceed when there are thousands of jobs that need the ownership changed? Here, I will use my brute force technique to generate the script to change the owner to sa. Remember, this is not the only way to work on it, There are many ways to skin a cat. I am using Brute Force techniques  becase this is very easy to follow.

Wait! before we generate a script, it is a safe practice to generate a rollback script. What is a rollback script?  The rollback script will change the owner back to previous stage. I always make a copy of rollback script before making any changes in a database. You can run the rollback when the things don't go as planned.

How to generate a rollback script?
-- Generate a script to change the ownership of a job to ......
select 'EXEC msdb.dbo.sp_update_job @job_name= N',''''+ name +''''
   ,','+'@owner_login_name=N','''' +SUSER_SNAME(owner_sid)+ '''' AS owner, ';' 
FROM msdb.dbo.sysjobs order by owner;

How to generate a script to change the owner to sa?
select 'EXEC msdb.dbo.sp_update_job @job_name= N',''''+ name +''''
   ,','+'@owner_login_name=N','''' +'sa'+ '''' AS owner, ';' 
FROM msdb.dbo.sysjobs order by owner;

Bonus:
How to list  all the disabled jobs?
SELECT
@@servername as ServerName,
name as JobName,
date_modified as LastModifiedDate
FROM
msdb.dbo.sysjobs WITH (NOLOCK)
WHERE
enabled = 0;

Note 0 disabled
         1= Enabled
Another way is to use your standard SSMS tool under  SQL Server Agents  ->Jobs,
look for icon with red arrow pointing down.

In this blog, you learnt to display the owner of a job,  generate backup script, an actual change script. Remember, the job does not run as the owner. We will discuss more on next blog and I hope you find this blog helpful.