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.

[SQL]

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

[/SQL]