Facility9

Ever have someone leave the company only to find out that they own critical database processes… because that process failed? It hasn’t happened to me, yet, but a recent Active Directory outage this weekend got me thinking about it.

The network admins made some upgrades to our Active Directory Servers over the weekend. After getting a few errors that jobs couldn’t run under a certain security context (my manager’s account) I put two and two together and realized that the SQL Server wouldn’t be able to authenticate against Active Directory if the server was down. I called my boss and confirmed that 1) he hadn’t been fired and 2) I was seeing what I thought I was seeing.

Once I was done with the phone call, I jotted down a note to look into this on Monday and went back to blasting the crap out of bad guys in the ASCII version of Halo. Fast forward to this morning. How would I go about figuring out which jobs were running under the wrong credentials? Magic.

Okay, it’s not really magic. We just pull back the database owner SID and compare that to the owners of any jobs. Once we know which jobs don’t belong to the current database owner, we can go ahead an change the job’s owner using sp_update job.

DECLARE @db_owner_sid AS VARBINARY(85) ;

SELECT  @db_owner_sid = s.SID
FROM    sys.sysusers AS s
WHERE   s.uid = 1 ;

SELECT  SUSER_SNAME(sj.owner_sid) ,
        name ,
        enabled
FROM    msdb..sysjobs AS sj
WHERE   sj.owner_sid <> @db_owner_sid ;
GO

EXEC msdb..sp_update_job
  @job_name = 'the job name',
  @owner_login_name = 'the new owner'

It’s important to keep in mind that we don’t really care who owns the job, we just want to make sure that it’s not a user who can get fired. No, Mister DBA, that doesn’t mean you. It means any non-system/service account. We want to make sure that everything is running safe and sound and that no nasty surprises will happen when you go on vacation in India for a month to find your spiritual foundation or whatever your dirty hippy ass is going to do for a month in India.