Script to find and update Agent Job Owners

Script to find and update Agent Job Owners
Download PDF

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:

SteveStedman5
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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*