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.

Solarwinds
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
WHERE l.name IS NOT NULL
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 ;
GO
EXEC dbo.sp_manage_jobs_by_login
@action = N'REASSIGN',
@current_owner_login_name = N'sa'
@new_owner_login_name = N'QUICK\Pinal', ;
GO

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)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – Display Rupee Symbol in SSMS
Next Post
SQL SERVER – Details About SQL Jobs and Job Schedules

Related Posts

1 Comment. 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.

    Reply

Leave a Reply

Menu