Why should you care about SQL Agent job’s owner?

Category: Security
Item:  SQL Agent jobs owned by non SA account

Users leave the organization.

When login is disabled, deleted or Active Directory is not available – the job may stop working.

The best practices recommend you set all job owners to SA account.

Note: You might have a different approach, such as creating an account or login specifically for SQL Agent jobs.

How to verify The Owner Of All SQL Server Agent Jobs

You may use the query below to check the jobs and their owners.

  1. SELECT sj.name AS [Job Name],
  2. sj.[description] AS [Job Description],
  3. SUSER_SNAME(sj.owner_sid) AS [Job Owner],
  4. sj.date_created AS [Date Created],
  5. sj.[enabled] AS [Job Enabled],
  6. sc.name AS [Category Name]
  7. FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)
  8. INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)
  9. ON sj.category_id = sc.category_id
  10. ORDER BY sj.name OPTION (RECOMPILE);

How to change SQL Server Agent Jobs Ownership?

  1. Using SSMS, click on Agent SERVER.
  2. Expand “Jobs,” and right-click the Job Name and select properties;
  3. Change the property owner to SA.

More information

Microsoft – Give Others Ownership of a Job

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.