Logging database changes using SQL Server audit

There is a need in recent work that a more important business table is often inexplicably changed. In SQL Server, this kind of work cannot be done without capturing records beforehand. For capturing changes, the options you can consider include TRACE,CDC. However, the cost of trace is relatively large, it is not appropriate for a system with a higher load, and the CDC needs to affect the business library, so SQL Server Audit is a good choice.

In SQL Server, if you just want to get the update time for the table, just look at the last update time of the table's clustered index, the code is as follows:

SELECT object_name (object_id) as DatabaseName, last_user_update,*
From Sys.dm_db_index_usage_stats
WHERE database_id = db_id (' Datebasename ')
and object_id=object_id (' TableName ')

This way, however, it is not possible to see a table modified by someone at some time, using server Audit. The Server audit is based on extended events, and the storage structure can be separate files from the user library, so it is not only good performance, but also does not affect the user library.

Here is the T-SQL code that enables auditing:

Use master
CREATE SERVER AUDIT audit1 to FILE (filepath= ' D:\SQLAudit ')
Use AdventureWorks2012
CREATE DATABASE AUDIT specification serialpic for SERVER AUDIT audit1
ADD (Update,insert,delete on person.address by dbo)

Use master
CREATE SERVER AUDIT audit1 to FILE (filepath= ' D:\SQLAudit ')
Use AdventureWorks2012
CREATE DATABASE AUDIT specification serialpic for SERVER AUDIT audit1
ADD (Update,insert,delete on person.address by dbo)

The code above first creates a server-level audit and deposits it in D:\SQLAudit, and then corresponds to creating a database-level audit. In a database-level audit, trace the update,insert,delete operation of the Person.Address table.

Next try to modify the database person.address, under Security-auditing, review the audit log, as shown in 1.

Figure 1. View Audit logs

As shown in result 2.

Figure 2: Database Audit records

This will allow you to see who has made changes to the table at what time. Of course, in addition to the UI approach, you can view audit records in T-SQL mode.

SELECT * FROM
Fn_get_audit_file (' D:\SQLAudit\audit1_B8A7821A-D735-446D-B6FA-DF582AB80375_0_130648999540780000.sqlaudit ', Default, default)

Logging database changes using SQL Server audit