Database level audit requirements

[SAPSupport]

Dear Team,

We have enabled database level auditing for our MSSQL database for INSERT, UPDATE & DELETE queries for all database users.

We are having following further requirements on the auditing part

1. We need change log/history of all audit trails including old and new values.
2. We want to activate individual table level database auditing.
3. We are unable to read generated database audit files. How to read the data captured in these audit files.

Expecting your inputs to achieve the mentioned settings in audit policies.

Thanks,

SAP BASIS Team

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

To achieve the requirements for auditing in your MSSQL database, you can follow these steps:

1. Change Log/History of Audit Trails Including Old and New Values

To capture old and new values for INSERT, UPDATE, and DELETE operations, you can use triggers along with auditing. Triggers can help you log the changes into a separate audit table.

Example Trigger for UPDATE:

CREATE TABLE dbo.AuditLog (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(128),
    Operation NVARCHAR(50),
    OldValue NVARCHAR(MAX),
    NewValue NVARCHAR(MAX),
    ChangedBy NVARCHAR(128),
    ChangedOn DATETIME
);

CREATE TRIGGER trgAuditUpdate
ON dbo.YourTable
AFTER UPDATE
AS
BEGIN
    DECLARE @OldValue NVARCHAR(MAX), @NewValue NVARCHAR(MAX);

    SELECT @OldValue = (SELECT * FROM deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
    SELECT @NewValue = (SELECT * FROM inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);

    INSERT INTO dbo.AuditLog (TableName, Operation, OldValue, NewValue, ChangedBy, ChangedOn)
    VALUES ('YourTable', 'UPDATE', @OldValue, @NewValue, SUSER_SNAME(), GETDATE());
END;

Example Trigger for INSERT:

CREATE TRIGGER trgAuditInsert
ON dbo.YourTable
AFTER INSERT
AS
BEGIN
    DECLARE @NewValue NVARCHAR(MAX);

    SELECT @NewValue = (SELECT * FROM inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);

    INSERT INTO dbo.AuditLog (TableName, Operation, OldValue, NewValue, ChangedBy, ChangedOn)
    VALUES ('YourTable', 'INSERT', NULL, @NewValue, SUSER_SNAME(), GETDATE());
END;

Example Trigger for DELETE:

CREATE TRIGGER trgAuditDelete
ON dbo.YourTable
AFTER DELETE
AS
BEGIN
    DECLARE @OldValue NVARCHAR(MAX);

    SELECT @OldValue = (SELECT * FROM deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);

    INSERT INTO dbo.AuditLog (TableName, Operation, OldValue, NewValue, ChangedBy, ChangedOn)
    VALUES ('YourTable', 'DELETE', @OldValue, NULL, SUSER_SNAME(), GETDATE());
END;

2. Individual Table Level Database Auditing

To activate auditing at the individual table level, you can create audit specifications for each table.

Example:

-- Create a server audit
CREATE SERVER AUDIT MyServerAudit
TO FILE (FILEPATH = 'C:\AuditLogs\MyAuditLogs');

-- Enable the server audit
ALTER SERVER AUDIT MyServerAudit WITH (STATE = ON);

-- Create a database audit specification for a specific table
CREATE DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpec
FOR SERVER AUDIT MyServerAudit
ADD (INSERT, UPDATE, DELETE ON dbo.YourTable BY [public]);

-- Enable the database audit specification
ALTER DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpec WITH (STATE = ON);

3. Reading Generated Database Audit Files

To read the data captured in the audit files, you can use the sys.fn_get_audit_file function.

Example:

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

This function reads the audit files and returns the audit records. You can filter and process these records as needed.