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!
Leave a Reply