Is there a way to query the owner of all jobs in SQL Server 2008 R2? I discovered when maintenance plans are edited the owner gets changed, so I want to make sure they are all owned by sa
.
3 Answers
This will generate the commands to change all non-sa-owned jobs to sa. You just need to copy and paste from the results pane to the top pane (or a new query window), and execute.
SELECT 'EXEC msdb.dbo.sp_update_job @job_id = '''
+ CONVERT(VARCHAR(36), job_id)
+ ''', @owner_login_name = ''sa'';'
FROM msdb.dbo.sysjobs
WHERE owner_sid <> 0x01;