How to Audit SQL Audit

While I was preparing my session for my next TechNet Live Meeting – Who did what and when on my database?, I was curious how you could audit the SQL audit. Actually, my goal was to be notified if my audit object layout was changed, disabled or dropped.

First thing that you can do is configuring the action group AUDIT_CHANGE_GROUP in the server and database audit specification.  This action group will log an event whenever and audit is created, modified or deleted.  OK, fine, nice feature, however… If somebody changes or disables the audit, I will not be notified about it.  It will be in just the audit log… Unless I verify the audit logs, I will never be aware about the changes that have been made.

So I was thinking of a way how I could be alerted in a fast way if somebody or something modifies the server or database audits. I know that there are other tools that are made for monitoring and alerting. For example, Microsoft System Center.  A possible solution would be writing the audit events to the application log or security log of the SQL Server.  The System Center Operations Manager is dedicated to monitoring, reporting and alerting on Windows event logs. So, problem solved! However, not every small company has those tools available…

I was looking for a solution within SQL Server to send me an alert if my audit object has been changed.

” … to send me an alert…”. SQL Server Agent can handle alerts!

Let’s see how we can configure this.  I have the following configuration

Server audit with name “Server Audit”
Server audit specification with name “Server audit specification”
Database audit specification with name “Database audit specification”

I’ve created 2 alerts, one that will capture all the modifications on my server audit objects and one that will capture the changes on my database audit object. I have used the event classes Audit Database Object Management Event Class and Audit Server Object Management Event Class for my WMI query.  Be aware when you use the where_condition that you can only use the = operand together with DatabaseName, SchemaName and ObjectName.  Other expressions cannot be used with these event properties.

Audit Server Alert

Audit Database Alert

The response of each alert is the execution of a job.  The job is going to gather some information about the event as will send an E-mail.  You can find the script below to create the job.

USE [msdb]

/****** Object:  Job [Send Alert]    Script Date: 28/03/2012 14:56:54 ******/
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 28/03/2012 14:56:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Send Alert’,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Mail]    Script Date: 28/03/2012 14:56:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Mail’,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’declare @Msg varchar(8000)
declare @sub varchar(128)
declare @State smallint
declare @Statename varchar(128)
declare @StartTime varchar(30)
declare @Description varchar(512)

SELECT @Sub = ”Server Audit State of $(ESCAPE_NONE(WMI(ServerName))) changed!”
set @StartTime=SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,0,5) + ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,5,2)
+ ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,7,2)
+ ” ” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,9,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,11,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,13,2)
SELECT @Msg = ”ObjectName: $(ESCAPE_NONE(WMI(ObjectName)))”
+ CHAR(13)
+”Hostname: $(ESCAPE_NONE(WMI(Hostname)))”
+ CHAR(13)
+”Loginname: $(ESCAPE_NONE(WMI(Loginname)))”
+ CHAR(13)
+”NTDomainname: $(ESCAPE_NONE(WMI(NTDomainname)))”
+ CHAR(13)
+”NTUsername: $(ESCAPE_NONE(WMI(NTUsername)))”
+ CHAR(13)
+”StartTime: ” + @StartTime
+ CHAR(13)
+ ”Executed Statement:”
+ CHAR(13)
+ ”$(ESCAPE_NONE(WMI(TextData)))”
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”Pieter”,
@recipients = ””,
@body = @Msg,
@subject = @Sub’,
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave


Remark: Make sure that Database mail is configured correctly and that the setting “Replace tokens for all job responses to alerts” is checked.  You can find this setting by selecting the properties of the SQL Server Agent (SSMS –> Right click on SQL Server Agent –> Properties) and go to the Alert system tab.


I know that there are still some gaps in this solution, like “What will happen if somebody disables the alert or stops the SQL Server Agent”, but at least this blog can be considered as a start…

Happy auditing!

Leave a Reply

Your email address will not be published. Required fields are marked *