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.

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.

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 Login, 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

4 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.

  • 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. ;)


Leave a Reply

Exit mobile version