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)
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?
There’s a small bug in the second script. Comma missing at the end of the 5th line shouldn’t be on the 6th. ;)
Good catch. I fixed it.
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!