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!