SQL SERVER – Query to List All Jobs with Owners

The other day, I was helping my client of the Comprehensive Database Performance Health Check, and during the engagement, I provided a few scripts which they can use to tune their system. During the engagement suddenly a Jr. DBA asked me if I have a query that will list all the jobs in their system with the Job’s owner. It is a very common question for any organization as most of the large organizations have people changing jobs all the time.

SQL SERVER - Query to List All Jobs with Owners jobs-800x215

Here is the script which will list all the jobs in the system with their current owner. If you see any owner of the job on the list who is about to leave your organization or who no longer owns the job, it is a good idea to change the owner of the job for security purposes.

SELECT s.name AS JobName, l.name AS JobOwner
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
ORDER by l.name

Here is the output which I got in my current system. As my current system is a demo machine, I currently do not have many jobs here.

Let us see how we can assign all the jobs owned by one user to another user. Here is the quick script. For example, if you want to assign every job in a system that is owned by one user and reassign to another user you can run the following script.

USE msdb ;
EXEC dbo.sp_manage_jobs_by_login
@action = N'REASSIGN',
@current_owner_login_name = N'sa',
@new_owner_login_name = N'QUICK\Pinal';

In the above script, all the jobs which were owned by sa are now re-assigned to Windows user Quick\Admin.

SQL SERVER - Query to List All Jobs with Owners jobs2

I hope this blog post helps you to learn how to know the user of the job and change the owner of the job. Here are a few additional blog posts which are related to this blog post.

You can always connect with me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Jobs, , SQL Scripts, SQL Server, SQL Server Agent, SQL Server Security
Previous Post
SQL SERVER – Display Rupee Symbol in SSMS
Next Post
SQL SERVER – Details About SQL Jobs and Job Schedules

Related Posts

6 Comments. Leave new

  • I have had better luck using SUSER_SNAME(msdb..sysjobs.owner_sid) to identify job owner since the login that owns a job may no longer be a login on that instance.

    • Very good point and I just came back here to comment this because I ran into the same results since the colleague no longer worked here and consequently was not in syslogins due to cleanup.

  • This is a great way to approach this. however, I been using dbo.sp_help_job to achieve the same thing. do you think this method is better and why?

  • Carlos Sosa Albert
    August 4, 2021 3:07 am

    There’s a small bug in the second script. Comma missing at the end of the 5th line shouldn’t be on the 6th. ;)

  • John Holiday
    May 7, 2022 12:03 am

    We recently had an issue where we had a number of replication jobs with an owner that no longer had rights. The WHERE clause of NOT NULL on your query effectively made the outer join an inner join. So I didn’t catch my broken jobs, when I took the were clause off or changed it to IS NULL then it worked and found our broken jobs. Thank you for what you do Dave, you still save me lots of time!


Leave a Reply