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