If your SQL Server environment is anything like mine, you’ve got quite a few SQL Agent Jobs configured and running. As a result, doing an audit to figure out the owner of each job and determining if the owner needs to be reconfigured could take quite a while by going into each job individually. Fortunately Microsoft has provided a system table and view to make this much easier.

The dbo.sysjobs table stores the information for each scheduled Agent Job. This table, and the corresponding dbo.sysjobs_view view are located in the msdb system database. Additional information regarding user logins can also be queried from the dbo.syslogins table in the master system database.

The following script can be used to retrieve all active and inactive SQL Agent Jobs along with their corresponding owner.

SELECT sj.name AS Job_Name, sl.name AS Job_Owner FROM msdb.dbo.sysjobs_view sj LEFT JOIN master.dbo.syslogins sl ON sj.owner_sid = sl.sid ORDER BY sj.name 1 2 3 4 5 SELECT sj . name AS Job_Name , sl . name AS Job_Owner FROM msdb . dbo . sysjobs_view sj LEFT JOIN master . dbo . syslogins sl ON sj . owner_sid = sl . sid ORDER BY sj . name

After running the above script you should see results similar the the screenshot below.

It’s worth noting that even though the current owner of each of these jobs shows sa, this is not necessarily best practice. I am pulling these results from a lab environment. Though having the sa account as the owner is not the end of the world, it is recommended to use a service account for consistency. Regardless of that fact, you should absolutely have a service account configured for the SQL Server Agent service in SQL Configuration Manager.

One more thing worth noting. In the script provided above you will notice I used a LEFT JOIN against master.dbo.syslogins . This was done intentionally as this will allow the query to return records where the Job_Owner is NULL . Any record showing a NULL value here indicates that the owner of the applicable Agent Job is orphaned. In my experience these scenarios tend to be associated with jobs that have been long disabled, but it’s certainly worth researching and updating the owner should you come across this situation.