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

2 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

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.