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 am looking for a solution to track object usage in my database. Objects such as tables, stored procedures, and functions.

I discovered SQL Auditing and was under the impression that it's exactly what I needed.

So I set it up to audit SELECT, INSERT, UPDATE, DELETE on my tables, and EXECUTE on my stored procedures and functions.

I set it up to target the Windows Security event log via Event Viewer Utility.

I made sure to allow permissions and set object access in Local Security Policy and using auditpol.exe.

Then I ran some INSERTs on tables and EXECUTEs on stored procedures. I then opened Event Viewer expecting to see logs of the time each of these statements where executed and on what object, however all I see is this:

enter image description here

Did I skip a step, am looking in the wrong place, or did I make a mistake from the beginning about what SQL Audit actually does?

EDIT: OK, I switch the target to a file and viewed the file via SSMS (right clicking Audit -> View Audit File)... and here the INSERTs and other actions are indeed being logged....So clearly I'm looking in the wrong place in the Event Viewer or missing something.

share|improve this question
    
I do a minor walk-through here: sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/… –  Aaron Bertrand Sep 13 '12 at 19:57
    
It doesn't look like you have it configured properly. When you have the target as the Security Log, you should see a source of INSTANCENAME$AUDIT. For instance, a default instance would have a source of MSSQLSERVER$AUDIT. The log would contain all of the information, including the statement that was used. You are looking at the right place in the Event Viewer, it just seems as though no audit events from SQL Server were logged. –  Thomas Stringer Sep 13 '12 at 20:05
    
Not sure if it helps, but just a suggestion, you might want to use SQL server profiler to set up a trace and target it to a file that can be analyzed later. –  Nazila Beikpour Sep 14 '12 at 10:26
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.