Query to find owners of SQL Server Agent jobs

To find owners of the jobs in SQL Server use this query (the query will show all jobs that have a valid owner):

SELECT J.name AS Job_Name
, L.name AS Job_Owner
FROM msdb.dbo.sysjobs_view J
INNER JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid

To list all jobs, even if the job owner was deleted from SQL Server, use a LEFT JOIN (thanks to your comments below). The query will show NULL values for orphaned job owners:


SELECT J.name AS Job_Name
, L.name AS Job_Owner
FROM msdb.dbo.sysjobs_view J
LEFT JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid

Stored procedure sp_help_job returns many other information about SQL Server Agent jobs:

EXEC msdb..sp_help_job

...or in SQL Server Management Studio, right click on SQL Server Agent job, and select Properties to find out the owner of the job:

If the post helped you, please share it:
Pin It

4 comments to Query to find owners of SQL Server Agent jobs

  • Rich

    I changed the INNER to a LEFT JOIN, then used a server group query to display orphaned job owners. Very handy when a Windows login was deleted...

  • FKP

    That's exactly what I wanted. Thanks

  • FKP

    We are in process of removing domain administrator account wherever it could be used on SQL server. One possible place was the SQL jobs; which this query solves; could you please tell me where else should I be looking?

    Thanks.

    • James

      You can check maintenance plans owners:
      USE msdb
      GO
      SELECT * FROM dbo.sysmaintplan_plans

      To change maintenance plan owner to 'sa':
      UPDATE msdb.dbo.sysssispackages
      SET [ownersid]=0x01
      WHERE [name]='Maintenance_Plan_Name'

      You can also check if the login is owner of any database or schema.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">