I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
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
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
A Script to Create a Script