Step 1: Create an Audit Object

Download PDF: Configure SQL Server Database Audit

USE master;
GO

CREATE SERVER AUDIT Audit_SQL_Tracking
TO FILE
(
    FILEPATH = 'C:\SQLAudit\',   -- make sure folder exists
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10
)
WITH (ON_FAILURE = CONTINUE);
GO

Step 2: Enable the Audit

ALTER SERVER AUDIT Audit_SQL_Tracking
WITH (STATE = ON);
GO

✅ 2. Create Database-Level Audit Specification

Now we define what actions to track inside a specific database.

USE YourDatabaseName;
GO

CREATE DATABASE AUDIT SPECIFICATION Audit_DB_Tracking
FOR SERVER AUDIT Audit_SQL_Tracking

✅ 3. Add Audit Actions (Your Requirements)

You asked to track:

• CREATE TABLE
• DROP TABLE
• DELETE
• CREATE PROCEDURE
• DROP PROCEDURE

Here’s the full script:

ALTER DATABASE AUDIT SPECIFICATION Audit_DB_Tracking
ADD (SCHEMA_OBJECT_CHANGE_GROUP),   -- CREATE / ALTER / DROP tables, procedures, views
ADD (DELETE ON DATABASE::YourDatabaseName BY PUBLIC),  -- DELETE operations
ADD (SCHEMA_OBJECT_ACCESS_GROUP);  -- Access tracking (optional but useful)
GO

What Each Option Means

✅ 4. Enable Database Audit

ALTER DATABASE AUDIT SPECIFICATION Audit_DB_Tracking
WITH (STATE = ON);
GO

✅ 5. View Audit Logs

You can read audit logs using:

SELECT *
FROM sys.fn_get_audit_file (
    'C:\SQLAudit\*.sqlaudit',
    DEFAULT,
    DEFAULT
);

✅ 6. Filter Specific Events (Example)

Only show DELETE operations:

SELECT event_time, action_id, statement
FROM sys.fn_get_audit_file (
    'C:\SQLAudit\*.sqlaudit',
    DEFAULT,
    DEFAULT
)
WHERE action_id = 'DL';  -- DELETE