patternsqlMinor

Query the Owner of SQL Server 2008 R2 Jobs

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

ownerthe2008sqlqueryjobsserver

Problem

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.

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#10587, answer score: 8

Revisions (0)

No revisions yet.