SQL Server Agent Job Ownership

By:   |   Comments (3)   |   Related: > SQL Server Agent


Problem

I am making a number of SQL Server security changes at the server and database level all at once, which is a problem in and of itself. I will be changing SQL Server service accounts, removing rights to BUILTIN\Administrators group, limiting specific user rights, moving to group based security, changing SQL Server Agent Job owners, etc.  Nevertheless, I want to prevent as many issues as possible with so many simultaneous changes.  One issue (of many) that I am concerned about is related to SQL Server Job ownership because our business is dependent on specific Jobs that need to run during the night during a very short window.  Ensuring that the SQL Server Jobs will run as expected is mission critical for us every day and especially when we make all of these changes.  As a first step, I want to find out who the SQL Server Job owners are and then determine if I need to make any changes to those SQL Server Jobs.  I know some jobs are owned by logins for people who have left the company, I know others are owned by miscellaneous logins and others are owned by the current SQL Server service account.  As such, how can I go about finding that information in an automated manner rather than clicking on each job one at a time in Enterprise Manager or Management Studio?  Should I do anything else?  Am I missing any steps?

Solution

First and foremost, be careful with the number of simultaneous changes.  If you cannot spread these changes out over a reasonable time period and test them one at a time, you might be setting yourself up for a major issue.  One issue can cascade into another issue and you may not be able to figure out the change that is ultimately responsible for the issue.  The volume and testing changes are ultimately up to you, but it is better to be safe than sorry.  Nevertheless, make sure you spend the time to analyze your environment, test appropriately and then push out the changes. So let's focus on the SQL Server Agent aspect of the task and break down the steps necessary.  These steps include the following:

  • Analysis
  • Decision
  • Rollback preparation
  • Changing SQL Server Agent Job ownership
  • Validating SQL Server Agent Job ownership
  • Testing and monitoring
  • If needed, rollback the changes

Analysis - SQL Server Agent Job Ownership

The script below will return the SQL Server Agent Job name, whether or not the job is enabled or not and the current job owner.  Once you run this code it would be wise to save the results as a point of reference.  This query can also be customized to meet your needs if you only want to find non-compliant job owners based on logic in your environment.  Just add a WHERE clause to meet your needs.

SQL Server 2000 and SQL Server 2005

USE MSDB
GO
SELECT GETDATE() AS 'ExecutionTime'
GO
SELECT @@SERVERNAME AS 'SQLServerInstance'
GO
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO

Decision - Determine the SQL Server Agent Job Needs

Once you have the SQL Server Agent Job ownership information, then you need to determine who should own the jobs.  Depending on the changes you are making, this portion of the process can be much different from instance to instance (IT utility SQL Server vs. line of business SQL Server) or environment to environment (Development, Test or Production).

Rollback Preparation - Backup the MSDB database

Depending on the scope of the changes, dictates the steps necessary for the rollback plan.  At a minimum, you should backup the MSDB database because all of the SQL Server Agent job information is stored in that database.  In addition, retain the scripts used to perform the analysis as well as to change the job ownership.  This will provide a reasonable means to rollback either at a specific job level or all jobs.  One key item here is when you take the backups.  Since most MSDB databases are small, the more backups the better.  So before you make job or user changes taking a backup would be wise.  Along those same lines, backing up the Master database would be wise because all login information is stored in that database.  So the combination of MSDB and Master databases should be taken and should serve as a last line of defense in case a rollback is needed.  Just be sure to include the backups as a portion of your change process.

For more sophisticated information on backup and restore needs, reference these tips.

Change the SQL Server Agent Job ownership

Two primary options are recommended to change the SQL Server Agent Job ownership.  First is a manual process where the jobs are changed via Management Studio or Enterprise Manager, as shown in example 1 and 2 below.  The third example is to execute the MSDB.dbo.sp_update_job (SQL Server 2000 and SQL Server 2005) system stored procedure to change the SQL Server Agent Job Ownership for a single Job.  The fourth example will dynamically build the code to issue the MSDB.dbo.sp_update_job system stored procedure based on the criteria of the Job not being owned by the sa login, but this code snippet can be modified to meet your needs.  Depending on number of changes, dictates the best approach in your scenario.

Example 1 - SQL Server 2000 Enterprise Manager

SQLServer2000EnterpriseManager Owner

 

Example 2 - SQL Server 2005 Management Studio

SQLServer2005ManagementStudio JobOwner

 

Example 3 - MSDB.dbo.sp_update_job - Single Job

EXEC MSDB.dbo.sp_update_job
@job_name = 'DailyBackups',
@owner_login_name = 'sa'
GO
Example Output:

When SQL Server Agent is not started, the code still appears to be successful even with this output:

SQLServerAgent is not currently running so it cannot be notified of this action.

When SQL Server Agent is started, below is the output:

The command(s) completed successfully.

Even if the Job is disabled, you still recieve this output:

The command(s) completed successfully.

 

Example 4 - Conditionally build code to correct all Jobs not owned by the sa login then review the output before executing the code in another SQL Server session to correct the SQL Server Agent Job ownership

SET NOCOUNT ON

SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + j.[Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE l.[name] <> 'sa'
ORDER BY j.[name]
Example Output:

EXEC MSDB.dbo.sp_update_job
@job_name = 'Full Backups - Daily',
@owner_login_name = 'sa'

EXEC MSDB.dbo.sp_update_job
@job_name = 'Full Backups - Policy Test',
@owner_login_name = 'sa'

Validate the SQL Server Agent Job ownership is accurate

The code below matches the code from the first section which can be issued after the job ownership changes to validate the SQL Server Agent Job ownership is accurate.  If you have saved the original scripts you can review the post change scripts as compared to the original scripts as well as validate the Job ownership is from the expected logins.

SQL Server 2000 and SQL Server 2005

USE MSDB
GO
SELECT GETDATE() AS 'ExecutionTime'
GO
SELECT @@SERVERNAME AS 'SQLServerInstance'
GO
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO

Testing and Monitoring - SQL Server Agent Job Ownership

Each of the Jobs should be tested and monitored to validate the changes have not caused any failures.  One approach is to let the jobs execute as normal and review the job status based on executing the scripts from the Finding SQL Server Agent Job Failures tip.  This code provides a simple means to review the failed Jobs on a specific SQL Server instance.  For the first few days after the job ownership changes and during specific time period processing i.e. month end, end of quarter, etc. it would make sense to monitor the job status.

Rollback - If needed, restore the MSDB database or change the job ownership to its original state

Hopefully, your scripts and changes were successful, if not, changing the specific job owners can be accomplished via Enterprise Manager or Management Studio.  If the problem is across the instance restoring the MSDB may be necessary.  Since it is a system database, the restore process is slightly different and could be a dedicated tip.  Until then, check out the following resources:

Next Steps
  • If SQL Server Agent Jobs are critical to you as many other organizations, be sure to plan for instance wide changes accordingly.  When you have a few Jobs, the management should be fairly straightforward.  If you have hundreds of SQL Server Agent Jobs across numerous SQL Servers, can quickly turn into a major issue.
  • Be sure to plan and build an accurate rollback plan.  It is essential especially if you have a small window of time to migrate the jobs ownership and complete the typical non-business hours processing.
  • Retaining the change scripts and backups is essential to have a history of the changes to troubleshoot any issues.  If you do encounter system wide or job specific issues, reviewing the historical information should shed light on the situation.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, June 26, 2013 - 2:24:47 AM - Paul Back To Top (25569)

There is a system stored procedure to do this in SQL 2005 and above. sp_manage_jobs_by_login.

 

USE msdb ;
GO

EXEC dbo.sp_manage_jobs_by_login
    @action = N'REASSIGN',
    @current_owner_login_name = N'danw',
    @new_owner_login_name = N'françoisa' ;
GO

Tuesday, September 25, 2012 - 3:03:47 PM - Cathy Back To Top (19678)

This does not work when the owner is a member of a Windows Security Group.  For example, I am in a group of SQL Administrators and own 96% of the jobs on the server, but this query does not retreive any of the jobs that I own.  That's because the security group name is the name in the syslogins table.

Tuesday, February 8, 2011 - 11:59:43 AM - Shannon Back To Top (12877)

I have tried numerous different ways to update or overwrite the job owner. Many times without any success. I found the best method was to delete the maintenance plan and then recreate it, logged in as the job owner.














get free sql tips
agree to terms