SQL Agent Job Ownership Notification

8

September 27, 2012 by Mike Hillwig

Last week, I was pulled in to help fix a server that had gone south. It was being managed by another group. These guys are great sys admins, but they’re not DBAs. The server’s hard drive was filling very rapidly, and it looked like SQL Server was the culprit. All of the lessons learned here could (and probably will) become a blog post series.

One of the most important things we saw was that all of the SQL Agent jobs were running with the user ID of an employee. Actually, it was a former employee. When his account got terminated, stuff started to break. You see, the application is pretty good about cleaning up after itself. That is until the SQL Agent jobs it creates stop working.

That’s the point in the story where one table grew to having 1.7 billion rows of data and backups didn’t run for six months.

Today, I now have a SQL Agent job that runs on EVERY server that I manage to look for ownership issues. (And I’m now managing this server.) One of the things it tells me is if any SQL Agent jobs are running as an account other than SA or the service account running the SQL Agent. That’s where the system_user function comes in very handy. When it’s called from a SQL Agent job, it will return the service account running the SQL Agent.

Oh, and look. It’s written using my “See something, say something” methodology.


IF EXISTS (
select sj.name, sl.name
from msdb.dbo.sysjobs sj
join master.dbo.syslogins sl
on sj.owner_sid = sl.sid
where sl.name NOT IN ('SA', system_user)
and sj.enabled = 1
)

BEGIN

DECLARE @v_dba varchar(100)

SELECT @v_dba = email_address
FROM msdb.dbo.sysoperators
WHERE name = 'SQLAgentOperator'

SELECT @v_dba = 'dbagroup@mycompany.com' WHERE @v_dba is null

DECLARE @v_output varchar(2000)
SELECT @v_output = 'The following SQL Agent Jobs have been found run as a user other than SA or the service account running SQL Server services. This is a risky practice because if the account running this job is disabled, then the job will cease to execute.' + char(10) + char(10) + 'How to handle this alert: This is NOT a critical alert and can be resolved by the next SQL Server DBA. It does NOT warrant getting someone out of bed.' + char(10) + char(10)
DECLARE @v_subject varchar(200)
SELECT @v_subject = 'SQL Agent Job Ownership Issue on ' + @@servername
DECLARE @v_job varchar(100)
DECLARE @v_owner varchar(100)
DECLARE c_jobs CURSOR FORWARD_ONLY FOR
SELECT sj.name, sl.name
FROM msdb.dbo.sysjobs sj
JOIN master.dbo.syslogins sl
ON sj.owner_sid = sl.sid
WHERE sl.name NOT IN ('SA', system_user)
AND sj.enabled = 1

OPEN c_jobs

WHILE (1 = 1)
BEGIN
FETCH NEXT FROM c_jobs
INTO @v_job, @v_owner

IF @@FETCH_STATUS <> 0
BREAK;

SELECT @v_output = @v_output + @v_job + ' (owned by ' + @v_owner + ')' + char(10)

END

CLOSE c_jobs
DEALLOCATE c_jobs

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dbmail',
@recipients=@v_dba,
@body=@v_output,
@subject=@v_subject ;

END

  • It looks like the last backed up file inomroatifn is not being updated in the log shipping tables using the windows auth proxy account but it is able to update the tables using the SQL autheticated user. You would want to check for double hop issues and login failed messages for the windows account when the backup job runs. The last step of the backup job is to update the backed up file inomroatifn in the log shipping history table. Use the Log Shipping standard report in Management Studio to identify if the last backed up file is being updated correctly when the windows account is used to run the job. My guess is NO.

  • I told my kids we’d play after I found what I needed. Damnit.

  • You’ve hit the ball out the park! Incredible!

  • Now I feel stupid. That’s cleared it up for me

  • That’s more than sensible! That’s a great post!

  • Absolutely, completely, totally and utterly incredible…and IMHO…self-incriminating. The first time a Judge holds an evidenciary hearing and THE ONE is AWOL!

  • wow I can’t believe the condition of that box! lol Glad your LOOT got to you safely! I LOVE scallops but never thought to eat them with polenta…interesting!

  • heh… good answer Karl!I compared your text in the answer to your main article, and I think the answer to our question here is bigger than the article itself.hehe….I think i’m going to have to try this memory lane into a timeline one day!