Owning it

In SQL Server when you create a SQL Agent job it will automatically list the creator of the job as the owner unless you explicitly change the owner. The owner of the job does not determine the credentials the job runs under in fact you can have a disabled SQL Server account own your SQL Agent jobs and they will run just fine however if you have them owned by a Windows account that is removed from the domain you have a problem.

A couple years ago another DBA in my company was primary on certain servers and I was primary on others. When he left the company I assumed duties on all the servers. This was a very busy time for me as I had to adjust to the added workload. Just as I thought I had things under control the SQL Agent jobs on what had been the other DBA’s servers started failing. It wasn’t panic time and I quickly identified the issue, the trouble was that there were a LOT of SQL Agent jobs that he owned and they all were starting to fail. Why didn’t they fail right away? Corporate policy is to disable the account and mark it for deletion then, after a few weeks to a month, the account is removed from Active Directory so until the account was actually deleted the jobs ran just fine. I changed a few jobs manually but the next day discovered more failed jobs. I needed a way to find all the jobs he owned quickly so examining each job individually was out. Where to turn? Why the system views of course. The following scripts will allow you to find out who owns your SQL Agent jobs:.

-- For SQL Server 2005
select ma.name as JobOwner, ms.name as JobName
from master.sys.syslogins ma
join msdb.dbo.sysdtspackages90 ms on ms.ownersid = ma.sid

--For SQL Server 2008 and newer
select ma.name as JobOwner, ms.name as JobName
from master.sys.syslogins ma
join msdb.dbo.sysjobs ms on ms.owner_sid = ma.sid

Finding the jobs was only half the battle, now I needed to change them to an account that would allow them to continue to run. I am not going to go into a discussion of what is the correct account to own your SQL Agent jobs in this post I will simply tell you what I do and why. As part of my server set up procedure I give the ‘sa’ account a strong password then disable it. I also go the additional step of renaming the account. The only servers that I don’t do this on are vendor systems where they require ‘sa’ to be used. Why? A quick Bing, or Google, search will find you many articles on the dangers of using ‘sa’. It is better since Microsoft removed the option of leaving the ‘sa’ password blank however when our security team showed me all the attempted attacks that hit our firewall trying to login using ‘sa’ it was an easy decision to make a policy to disable and rename the account. Why not just delete it? You can’t.

So now I have this disabled account just sitting there...why not put it to use. I use the disabled and renamed ‘sa’ account to own my SQL Agent jobs. Back to the story…after finding all the jobs that needed to be renamed I created another script to change the job owner from the deleted Windows account to the disabled/renamed ‘sa’ account:

--For SQL Server 2005
begin tran
update msdb.dbo.sysdtspackages90
set [ownersid]=0x01
from master.sys.syslogins ma
join msdb.dbo.sysdtspackages90 ms on ms.ownersid = ma.sid
where ma.name = 'DOMAIN\old_account'
-- commit

--For SQL Server 2008 and newer
begin tran
update msdb.dbo.sysjobs
set [owner_sid]=0x01
from master.sys.syslogins ma
join msdb.dbo.sysjobs ms on ms.owner_sid = ma.sid
where ma.name = 'DOMAIN\old_account'
-- commit

I like to wrap these in a transaction then after the update I run my select again to verify all jobs are owned by the correct account then commit my changes…YMMV depending on your snapshot isolation level.

You will also note that I used the owner_sid in my update. This is one other advantage of the disabled/renamed ‘sa’ in that the SID is always the same and made creating the script easy. You can use another user but the SID would be much more complicated and vary from instance to instance so you would want to modify the script to join in the syslogins view to pull the correct SID for the account you want to use.