Question

Enable Auditing in SQL Server and demonstrate - Part 1

Answer 1

inchirags@gmail.com   Chirag's SQL Server DBA Tutorial         https://www.chirags.in

*****************************************************************************************

* Enable Auditing in SQL Server and demonstrate - Part 1 *

*****************************************************************************************

YouTube Video:

For Part 2 - Add a new database to an existing audit specification in SQL Server

Enable auditing in SQL Server and demonstrate it with a step-by-step example, we will create a database, tables, perform CRUD operations, and log user activity. Here's how to do it:

Step 1: Create a Test Database

Run the following query to create a sample database:

CREATE DATABASE AuditDemoDB;
GO

USE AuditDemoDB;
GO

-- Create a sample table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50),
    Position NVARCHAR(50),
    Salary DECIMAL(10, 2)
);
GO

Step 2: Enable Server Audit

Server-level auditing is required to capture database-specific events.

    Create a Server Audit and specify the file location to save logs:

CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'C:\AuditLogs\', MAXSIZE = 10 MB);
GO

Enable the Server Audit:

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

Step 3: Create a Database Audit Specification

    Create an audit specification to track SELECT, INSERT, UPDATE, DELETE operations for a specific database:

CREATE DATABASE AUDIT SPECIFICATION DbAuditSpec
FOR SERVER AUDIT ServerAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON DATABASE::AuditDemoDB BY [public]);
GO

Enable the Database Audit Specification:

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

Step 4: Perform CRUD Operations

Perform some sample operations to generate audit logs:

USE AuditDemoDB;
GO

-- Insert records
INSERT INTO Employees (Name, Position, Salary) VALUES ('Chirag', 'DBA', 70000);
INSERT INTO Employees (Name, Position, Salary) VALUES ('Sanju', 'Teacher', 50000);
GO

-- Update a record
UPDATE Employees SET Salary = 55000 WHERE Name = 'Sanju';
GO

-- Select records
SELECT * FROM Employees;
GO

-- Delete a record
DELETE FROM Employees WHERE Name = 'Sanju';
GO

Step 5: View Audit Logs

Audit logs can be queried or viewed in SSMS:

1. Query Audit Logs using T-SQL

Use the following query to read the audit logs:

SELECT event_time,
       action_id,
       succeeded,
       object_name,
       statement,
       server_principal_name
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT);
GO

2. View Logs in SSMS

    Navigate to Security → Audits in SSMS.

    Right-click the ServerAudit and choose View Audit Logs.

Step 6: Disable or Drop Auditing

When auditing is no longer required, disable or drop it:

    Disable database and server audit:

ALTER DATABASE AUDIT SPECIFICATION DbAuditSpec WITH (STATE = OFF);
ALTER SERVER AUDIT ServerAudit WITH (STATE = OFF);
GO

Drop the audit configuration:

    DROP DATABASE AUDIT SPECIFICATION DbAuditSpec;
    DROP SERVER AUDIT ServerAudit;
    GO

Expected Output

    Logs will show user activity such as INSERT, SELECT, UPDATE, and DELETE statements.

    Fields in the log include timestamp, SQL statement executed, user name, and success status.

Let me know if you'd like further assistance!

For any doubts and query, please write on YouTube video comments section.

Note : Flow the Process shown in video.

Subscribe and like for more videos:

https://www.youtube.com/@chiragstutorial

Don't forget to, Follow, Like,  Share &, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

_________________________________________________________________________________________

Note: All scripts used in this demo will be available in our website.

Link will be available in description.