Finding SQL Agent Jobs Owned By The Wrong Person

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'