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

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.

By the way, this check is a part of our SQL Server Health Check service.

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

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

Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

One Response

  1. Actually, it’s not best practice for the sa account to own jobs. That causes problems with SSIS jobs and the use of proxy accounts. You should test out creating a dummy SQL account–probably one without logon privileges and with at most a SQLAgentUserRole role–to own your jobs.

Leave a Reply

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