Cleaning up sql job ownership

We have a lot of jobs owned by many different accounts due to the currently lax security. Before cleaning up the local admins group and removing all sysadmin role members it is important to make sure the jobs that are currently running with elevated privileges remain running with elevated privileges. Something like this site explains it well enough.

select sj.name , sl.name
from msdb..sysjobs sj join master..syslogins sl on sj.owner_sid = sl.sid
where sl.name <> ‘sa’

How to generate the sql to make those jobs be owned by sa:

select ‘exec sp_update_job @job_name = ”’ + sj.name + ”’ , @owner_login_name = ”sa”’
from msdb..sysjobs sj join master..syslogins sl on sj.owner_sid = sl.sid
where sl.name <> ‘sa’

This entry was posted in Day to day, DBA and tagged . Bookmark the permalink.

Leave a Reply

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