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:
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.
Best Answer
To track object use with the SQL Server Audit feature, it’s necessary to set up the auditing. In order to do so, an audit object must be created first.
This can be done using SQL Server Management Studio or T-SQL.
The next step is to set up the auditing in the particular database on specific objects. This is where availability of the SQL Server Audit feature is required on the database level (the database level auditing is available in SQL Server Enterprise and Developer editions only).
To continue setting up the auditing, it’s required to create a database level audit specification. Such database level audit specification will belong to the audit object previously created.
Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only.
So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object.