Create Database Audit for INSERT, UPDATE, DELETE in SQL Server

There are a few ways to monitor when someone makes an INSERT, UPDATE or DELETE in SQL Server. One quick and easy way is through SQL Server Audit. Below is a step-by-step way to create that audit:

Expand Security, then right-click Audits and click ‘New Audit…’

Pick how, and where, you want to save the file.

After choosing where/how to save the file, expand the database, then expand Security, right-click ‘Database Audit Specifications‘ and choose ‘New Database Audit Specification‘

The next set of sequences all relate to what objects/permissions/roles you want to audit.

Output of fn_get_audit_file:

The code to recreate the above steps is below:

use master;
GO

IF DATABASEPROPERTYEX('testDB', 'version') > 0
BEGIN
    ALTER DATABASE testDB SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE testDB;	
END


CREATE DATABASE testDB;
GO


use testDB;
GO


CREATE TABLE testTable(
col1 int IDENTITY,
col2 char(20));
GO

--Insert Data
INSERT INTO testTable
VALUES('Insert Test');
GO

--Update that data
UPDATE testTable
SET col2 = 'Update Test'
WHERE col2 = 'Insert Test';
GO

--Delete that data
DELETE testTable
WHERE col2 = 'Update Test';
GO

--Now check 'fn_get_audit_file'
--to see the above Insert, Update, Delete

SELECT aud.event_time,
aud.action_id,
aud.session_id AS [Session ID],
aud.server_principal_name,
aud.database_name AS [Database],
aud.schema_name AS [Schema],
aud.object_name AS [Object],
aud.statement
FROM fn_get_audit_file('E:\Backups\testDB\IUD-Audit_810D2516-4695-4824-8384-EFC19C99C62E_0_130969930856890000.sqlaudit',DEFAULT, DEFAULT) aud


--Drop table
DROP TABLE testTable;
GO