SQL Auditing via Trace

Last Post 17 May 2006 05:39 AM by SQL_Jr. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
SQL_Jr
New Member
New Member

--
16 May 2006 07:18 AM
Ok, I need help with the scheduling part. I have decided to use this pretty cool method I found on sqlservercentral.com for SQL Auditing and Notification

I will use it for a request to audit for anyone acessing a particular table (Insert, Update, Delete and Select. The reason being, is the data is very sensitive, and they want a log to see if anyone's accessed it. The method used is

There are four major components in building this security model using scripted trace.

    An auditing trace is established to monitor any concerned events

    A job is setup to export the trace file to a trace table every fixed period

    An alert is created with notification functions set

    A trigger on the trace table will fire out notifications (email, pager) to concerned people by raising a low severity error.


I will insert data using ::fn_trace_gettable(path to .trc) file, and search for criteria there. This insert operation will be scheduled and seems can only be done when the trace is stopped. Basically, I want to ensure a continuous auditing environment, and that I get all the data in the table before the trace file rolls over. Any help is appreciated. Thx!
SQL_Jr
New Member
New Member

--
17 May 2006 05:39 AM
Nobody with ideas? I'll try to get right to what I need using client side tracing......

Should I use rollover option? How would I control the rollover of the .trc file for data insertion to a table, and ensure continuity.

There, two lines. Please advise. Tx


Acceptable Use Policy
---