SQL Server 2016 audit capabilities

Question

I have a typical web-application, very CRUD based. When certain tables or columns change, we need to store history information about it. This could e.g. be an AUDIT table containing new/old column values, timestamp, what table it relates to, name of user who changed it, etc.

Typically I have implemented this e.g. with Triggers to get this information and store to an AUDIT table.

However, could this same be achieved automatically with SQL Server 2016 audit features? A few questions come to my mind: 1) is it able to store the audit data in a database table, as in above example with triggers? 2) is an application allowed to read the data, or does accessing the audited data require some special admin credentials? 3) is it possible to specify what information needs to be logged, or does the functionality log everything, automatically?

Is the purpose of this data *audit* or *history*? For history, you might want to consider the new temporal tables feature, if that fits. — Damien_The_Unbeliever, Oct 05 '16 at 07:34
I see history and audit to be the same thing. Key points are that 1) we probably need to be able to define the table structure, 2) application pages need to be able to query the audit/temporary tables — user1340582, Oct 05 '16 at 08:11
For history tables, as Damien offered best way is using Temporal tables http://www.kodyaz.com/sql-server-2016/create-sql-server-2016-temporal-table.aspx on SQL Server 2016. Unfortunately these tables does not have the user name who caused the change — Eralper, Oct 05 '16 at 08:21

score 0 · Answer 1 · answered Oct 05 '16 at 08:38

You can use triggers to keep track of data changes on a database table You can find an example of how to log sql table data changes using triggers at referred tutorial

Here you need to know, if the users of your applications are mapped to a single user on database, you will have to pass insertedby, updatedby or deletedby user information explicitely in your UPDATE,DELETE or INSERT commands as table field values. Otherwise, what you will get will be the same user for all transactions

Just like temporal tables on SQL Server 2016, you are required to use or create your history table or log table manually

Please check following SQL trigger code

CREATE TRIGGER dbo.LogTbl
    ON dbo.Tbl
AFTER INSERT, UPDATE, DELETE
AS

IF EXISTS (
  SELECT * FROM Inserted
) -- INSERT or UPDATE
BEGIN
    IF EXISTS (
      SELECT * FROM Deleted
    )
  -- UPDATE Statement was executed
  INSERT INTO HistoryTbl (
    ...
    UpdatedDate,
    UpdatedByUserId
  )
  SELECT
    ...
    i.UpdatedDate,
    i.UpdatedByUserId
  FROM Deleted d
  INNER JOIN Inserted i ON i.Id = d.Id

  ELSE
  -- INSERT Statement was executed
  INSERT INTO HistoryTbl (
    ...
    InsertedDate,
    InsertedByUserId
  )
  SELECT
    ...
    i.InsertedDate,
    i.InsertedByUserId
  FROM Inserted i

END
ELSE
  -- DELETE Statement was executed
  INSERT INTO HistoryTbl (
    ...
    DeletedDate,
    DeletedByUserId
  )
  SELECT
    GETDATE(),  ---!!!
    USER_ID()   ---!!!
  FROM Deleted

GO

Pay attention to DELETE command, in delete command we don't have deletedby user data. So I used USER_ID(), you can use the USER_NAME() too

An other approach can be, instead of DELETE'ing a row, you can DELETE it by UPDATE'ing an ACTIVE column value to false. I mean UPDATE data, it you set its active field to 0, this can be thought as it is deleted.

So you have to modify all your codes by selecting only active data...

Yes triggers is one approach and I've used it before, but now I wonder if we could use SQL Server 2016 capabilities for this out-of-the-box? Triggers can cause all kinds of performance issues and confusion... — user1340582, Oct 05 '16 at 08:53

SQL Server 2016 audit capabilities

1 Answers1