Query the Owner of SQL Server 2008 R2 Jobs

Question

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.

Answer 1

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;

Answer 2

SELECT 
     name
   , SUSER_SNAME(owner_sid) AS owner 
FROM msdb.dbo.sysjobs;

Answer 3

select 
    sj.name,
    description,
    SUSER_sNAME(owner_sid)
from msdb.dbo.sysjobs sj;