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 = 'firstname.lastname@example.org' 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