Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.


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?

share|improve this question
add comment

3 Answers

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.

share|improve this answer
    
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 '12 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 –  OliverAsmus Mar 26 '12 at 12:30
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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