Identify the owner and then change the owner of a SQL Agent Job

In this post and video, you will learn how to identify the owner and then change the owner of a SQL Agent Job.

Have you ever had issues where the owner of a SQL Agent job leaves and their windows active directory account gets deleted/removed/disabled and the job(s) they own then stops working?
I was doing some SQL Server security work for a client recently. Part of that process involved changing the authentication mode of SQL Server from Mixed Mode to Windows only, disabling the built in SA account and locking things and removing components not used to reduce the surface area.

This process involved cleaning up old logins but some of the logins I wanted to remove were the owner of some SQL Agent jobs that were still needed and executing on a schedule. I needed to change the SQL Agent job owner for these jobs.

There is an argument  for setting all jobs to be owned by SA, you might have a different approach such as creating an account or login specifically for owning SQL Agent jobs – whatever approach you take you might want to avoid having jobs owned by users – if the users leaves the job might just unexpectedly stop working

The first thing I wanted to do was identify all the jobs on the instance and their respective owners – allowing me examine the problem

List job name and owner

The following query return the owners of a SQL Agent Job. It’s a join query getting data from the msdb.dbo.sysjobs system table and the DMV sys.server_principals

SELECT J.name AS Job_Name
, P.name AS Job_Owner
FROM msdb.dbo.sysjobs J
INNER JOIN
sys.server_principals P

 

ON J.owner_sid = P.sid

You can see from the results below that the owner of my all my jobs is a login called Gethyn

Just in case Gethyn leaves, or I need to move the job to another server which doesn’t have a login called Gethyn on it or its created with a different SID to the current server I want to change all these jobs to be owned by SA

Updating the SQL Agent job owner

There is stored procedure in MSDB that allows you to change the attributes of Agent jobs including updating the SQL Agent job owner. The stored procedure is called SP_UPDATE_JOB.

A Script to Create a Script

The next script is going to generate a script that is going use the SP_UPDATE_JOB stored procedure to change the job name.
I could wrap this in some dynamic SQL but I want to check my script manually before I execute the script. It will also give me some documentation as to what I have changed too.
It’s a SELECT statement that concatenates the TSQL needed to execute SP_UPDATE_JOB and passes some of the values of a SELECT from the first query above, providing a final statement to change the job owner of the jobs to SA.
Before executing the below I change the query result output to be text (not grid) then execute the query
SELECT  ‘exec msdb..sp_update_job
        @job_name = ”’+j.name + ”’,
        @owner_login_name = ”sa”
        ;’
FROM msdb.dbo.sysjobs J
INNER JOIN
sys.server_principals P
ON J.owner_sid = P.sid
Execute the query select all the text in the result pane to text in management studio, take the results of the query and paste into a new query window. Execute the script pasted into the  new query window to rename the necessary jobs.
exec msdb..sp_update_job
        @job_name = ‘Test’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘syspolicy_purge_history’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘DatabaseBackup – USER_DATABASES – LOG’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘DatabaseIntegrityCheck – SYSTEM_DATABASES’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘DatabaseBackup – USER_DATABASES – FULL’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘DatabaseBackup – SYSTEM_DATABASES – FULL’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘Output File Cleanup’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘DatabaseBackup – USER_DATABASES – DIFF’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘sp_purge_jobhistory’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘CommandLog Cleanup’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘DatabaseIntegrityCheck – USER_DATABASES’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘sp_delete_backuphistory’,
        @owner_login_name = ‘sa’
        ;
exec msdb..sp_update_job
        @job_name = ‘IndexOptimize – USER_DATABASES’,
        @owner_login_name = ‘sa’
If you re-run the first query that shows us the list of jobs and their respective owners you can see that they are all now owned by the SA account.
If you need help with you SQL Servers please check out our SQL Server Consulting page, or use the book a free appointment

2 Comments

Submit a Comment

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