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

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.

• SQL SERVER – T-SQL Script to Check SQL Server Job History
• How to List All the SQL Server Jobs When Agent is Disabled?
• SQL SERVER – SQL Agent Job and Backslash – Strange Behavior
• How to Schedule a Job in SQL Server?
• SQL SERVER – Displaying SQL Agent Jobs Running at a Specific Time

You can always connect with me on Twitter.

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