4


I'm setting up SQL Server 2008 Audit Server to log any activities made by certain group of users of certain database. All I need to do is get notified when the Audit server logs something, so that I can send out emails to the admins notifying them of the logged operation.

Is there a way -an event that get raised or something- to tell SQL Server to run a certain job once an audit entry has been recorded?

3 Answers 3

1

One consideration would be to query the sys.dm_audit_actions view at regular intervals to see what new information has been logged to the audit log. For example, create a stored procedure to mail the results of a query from this view to a set of people on a daily basis. The only drawback would be that it wouldn't be delivered as it happens (asynchronously) unless you wanted to script it in .Net.

2
  • I took a look at sys.dm_audit_actions and this actually isn't the viewer i'm using to see the log. I'm viewing the logs using sys.fn_get_audit_file since i'm keeping the audit logs in a file. I'm implementing the audit log like in this article. Finally could you elaborate on scripting this on .NET, i'm a programmer so that would be easier for me.
    – Galilyou
    Mar 25, 2012 at 5:38
  • Hmm . . . it's been a while since I implemented the Event Viewer class in .NET but I believe it is in System.Diagnostics.Eventing.Reader. I only suggested it since SQL Server lacks in some of these higher level async functions. There is a sample here: msdn.microsoft.com/en-us/library/bb671203(v=vs.90).aspx Mar 26, 2012 at 12:30
1

You can log the events to the Windows logs or the Security log and then have an Alert set up for that event. The Alert would be set up to notify you via email. The following code was for someone looking to solve a little different problem but my response was to use what you are asking.

Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

--Create the Server Audit
USE master
GO
CREATE SERVER AUDIT BackupTrap
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 0, ON_FAILURE = CONTINUE)
GO

--Turn the Audit On
ALTER SERVER AUDIT BackupTrap
WITH (STATE = ON)
GO

--Create the Database Audit Specification
USE AdventureWorks2012
GO
CREATE DATABASE AUDIT SPECIFICATION BackupTrapAdventureWorks
FOR SERVER AUDIT BackupTrap
    ADD (BACKUP_RESTORE_GROUP)
WITH (STATE = ON)
GO

--Create the job to run
USE msdb
GO
EXECUTE dbo.sp_add_job
    @job_name = N'BackupAlertJob'
GO

EXECUTE dbo.sp_add_jobserver
    @job_name = N'BackupAlertJob'
GO

EXECUTE dbo.sp_add_jobstep
    @job_name = N'BackupAlertJob',
    @step_name = N'RunSP',
    @subsystem = N'TSQL',
    @command = N'EXECUTE dbo.MyStoredProcedure',
    @database_name = N'AdventureWorks2012'
GO
0

If SQL Server Audit is used to capture changes on instances, objects, and data, there is a way to create an appropriate alert by utilizing SQL Server Agent jobs. A job should be scheduled to perform as often as possible, every minute for instance, but that depends on how often you need to check the appropriate SQL Server Audit repository file (a .sqlaudit file, application logs, or security logs) in search of important event. The job should execute a stored procedure that reads and analyzes the repository, and finds the events that you want to raise alerts for. If you’re storing audit logs into a file, use the fn_get_audit_file function to read it.

In case the events are found, an email can be sent with appropriate message. Note that for sending emails SQL Server Database mail must be enabled and set properly.

Downsides of this method are that it requires advanced T-SQL knowledge, it can use significant SQL Server resources depending on the schedule frequency and the size of the repository that needs to be read each time, and it has to be manually set up for each event type and database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.