How To Audit Data Changes In SQL Server?


Scenario:

I was at a new client, with their previous and only DBA / DEVELOPER/ MASTER OF ALL of 8 years all suddenly gone with no documentation.

Their business critical application was having data integrity issues. Some data were updated incorrectly while some others were not updated at all, showing old or incorrect data to their users/customers/vendors etc...

After going through their stored procedures, triggers, view, ETLs, SQL trace etc... I was able to track down and fix most of the data issues.

But there was one particular table that I could not figure out.  I thought maybe that table is never updated? How can I be sure what is going on with it?

I decided I need to setup something to automatically monitor this table for few days.  Here, SQL server offers few options:

  • Profiler trace
  • Server side trace
  • Extended events
  • Audits
  • Triggers
  • CDC/CTC
  • Service brokers
  • ..........


I knew that I only need to know how this particular table is being updated. I don't need to know the actual data per column that is being updated, which is where triggers, CDC, service brokers, trace etc... can come handy.

I decided that using the Audi feature would be the best option here for me. It would tell me exactly what I needed to know with as little overhead as possible, it was quick and easy to setup as well.

In the following sample script I am using it to audit data changes in a particular table. You could however use it to audit multiple tables and/or also audit actions against  Views, Stored procedures, Inline functions, Scalar functions, Table-valued functions, Aggregate functions, Sequences etc.



-- FIRST , WE NEED TO DEFINE THE AUDIT FILE WHERE THE LOG ENTRIES WILL BE STORED
-- TO CREATE SERVER AUDIT, YOU NEED TO BE IN THE MASTER DATABASE

USE [master]
GO

CREATE SERVER AUDIT [Audit-Data-Changes]
TO FILE
(      FILEPATH = N'E:\MSSQL\Audit\'
       ,MAXSIZE = 100 MB
       ,MAX_FILES = 5
       ,RESERVE_DISK_SPACE = OFF
)
WITH
(      QUEUE_DELAY = 1000
       ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-Data-Changes] WITH (STATE = ON)
GO

-- NOW SWITCH TO THE DATABASE WHERE THE TABLE IS
-- HERE WE ARE GOING TO DEFINE WHICH OBJECT AND APPLICABLE ACTIONS WE WANT TO LOG

USE [DB_NAME]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Data-Changes]
FOR SERVER AUDIT [Audit-Data-Changes]
ADD (DELETE ON OBJECT::[dbo].[Table1] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[Table1] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[Table1] BY [public])
WITH (STATE = ON)
GO

-- After running the Audit for two days, I ran the following query and there was the information I needed.

-- READING THE LOG ENTRIES

DECLARE @audit_name NVARCHAR(1000) = 'Audit-Data-Changes';
DECLARE @audit_log_file NVARCHAR(4000)


SELECT @audit_log_file  = CONCAT(log_file_path, name,'*') FROM     sys.server_file_audits WHERE    name = @audit_name AND is_state_enabled = 1 ORDER BY audit_id DESC
                                        
SELECT  ac.name ,ac.class_desc ,alf.* FROM    sys.fn_get_audit_file(@audit_log_file, DEFAULT, DEFAULT) alf
        LEFT JOIN sys.dm_audit_actions ac ON ac.action_id = alf.action_id AND ac.action_in_log = 1
              ORDER BY event_time DESC;
GO 




Comments

Most Popular

DMV To List Foreign Keys With No Index

Easy to use WMI commands to query windows system

Generate SQL script to extract user permissions from a SQL database