Sql-server – Sql Server – How to convert Audit log into a table

auditsql serverstandard-edition

I am using Sql Server 2014, Standard Edition. My objective is to trace the history of operations on the tables (insert, update, delete), and to display on the web so the users are able to tell what happened to the data in the past.

So, I came to know there is a partial support of Audit feature in the Sql Server Standard Edition, and was able to log some operations, as attached.

enter image description here

My question is, how to make those logs stored inside a proper Sql Table so that the records could be accessed, retrieved and displayed on the web page?

Currently, the Audit Destination allowed:
File, Security Log, Application Log
… which don't seem to be able to be retrieved directly from an ASP.NET API.

Is it possible to log into an SQL Table?

P.S.:

(Correct me if I am wrong). Many have said that the Standard Edition only allows Server-Level Audit, but NOT Database Level Audit. However, for some unknown reasons, if I added these 7 audit types (yup, 7 or none), I am able to capture and view the database level operations.

enter image description here

Best Answer

Capturing DML operations on individual tables is among the auditing features that are not available in Standard Edition.

If you want to trace DML operations, another possible hook is Extended Events. You won't be able to use the auditing package, but you could use lock acquired events to capture read and write operations.

I blogged about it here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

Other options are:

  • Triggers: this is easy to set up, even automatically by generating the trigger code. Downside is the increased write overhead.
  • App-side logging: If you have control over the application, you can inject the code needed to log accesses to the tables.
Related Question