Script to find and update Agent Job Owners

Here is a quick script to find the SQL Server agent job owners.

SELECT s.name AS JobName,
l.name AS JobOwner
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
WHERE l.name IS NOT NULL

Once you have that if you need to update the job owner, here is the script that I use. For instance If I wanted to change the owner from sa to NewSA I would use this script, and copy the third column and execute that code.

 SELECT s.name AS JobName, 
		l.name AS JobOwner, 
		concat('exec msdb..sp_update_job @job_name = ''', s.name, ''',  @owner_login_name = ''NewSA''; ') as sql
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
WHERE l.name IS NOT NULL
and l.name like 'sa';

When you copy the SQL column, the code looks like this.

exec msdb..sp_update_job @job_name = 'Job0',  @owner_login_name = 'NewSA'; 

exec msdb..sp_update_job @job_name = 'Job1',  @owner_login_name = 'NewSA'; 

exec msdb..sp_update_job @job_name = 'Job2',  @owner_login_name = 'NewSA'; 

exec msdb..sp_update_job @job_name = 'Job3',  @owner_login_name = 'NewSA'; 

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!