<< SQL Server Hosting - HostForLIFE :: How to put a SQL Server Database into Recovery Pending? | SQL Server Hosting - HostForLIFE :: SQL Aggregate Statement >>

SQL Server Hosting - HostForLIFE :: Customized Auditing In SQL For DDL Operations At Server Level

August 10, 2021 09:04 by author

Peter

In this article, we will learn how to implement the Audit feature in SQL server. This includes storing all the Audit information in a local table with respect to operations such as creating, altering, and dropping of all tables at the server level.

Why is Auditing essential?
Auditing is indeed required to keep track of all the changes to objects that took place within your SQL Server. It has all information such as event type, changed time, server name, login name, database name, object name and actual SQL script used to make the change.

How to achieve Auditing in SQL?
To capture DDL activities locally, you need to achieve the below steps,
    Creation of Trigger
    We need to create a trigger for all DDL activities such as Create, Alter and Drop operations at server level.

    Use of Event Data function
    This function helps to capture data associated with a DDL operation in an XML form which will be later used to store in a local table.

    Creation of a local Audit table
    We will be using a local table to store Audit data from the data returned by EventData function.

    Creation of Stored procedure
    A simple stored procedure is required to capture audit data into an XML parameter using EventData function and storing the same data into the local Audit table.

Detailed steps with SQL query to perform Auditing

First, we will create a local table named ‘AuditChanges’ to capture Audit changes into it using the below SQL script,
    CREATE TABLE [dbo].[AuditChanges]
    (
        [DatabaseName] [nchar](50) NULL,
        [TableName] [nchar](50) NULL,
        [EventType] [nchar](50) NULL,
        [LoginName] [nchar](50) NULL,
        [SchemaName] [nchar](50) NULL,
        [SQLCommand] [nchar](50) NULL,
        [CaptureTime] [nchar](50) NULL
    )

Now we have to create a trigger named ‘Audit_Trigger’ on server level which will store the event data as well as storing the information into AuditChanges table. Here [1] refers to the top 1 and datatype varchar means we are storing it into string.
    ALTER Trigger Audit_Trigger
    ON ALL SERVER
    FOR Create_Table, Alter_Table, Drop_Table
    As Begin
    DECLARE @EventData XML
    SELECT @EventData = EVENTDATA()
    INSERT INTO Test.dbo.AuditChanges
    (DatabaseName, TableName, EventType, LoginName, SchemaName, SQLCommand, CaptureTime)
    Values
    (
       @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(50)'),
       @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(50)'),
       @EventData.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),
       @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)'),
       @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(50)'),
       @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(50)'),
       GETDATE()
    )
    END

All setup is done. Now we will be creating a table named 'Student' to verify if event data relating to its creation is getting stored in AuditChanges table or not. Below is the query to create the table student.
    CREATE TABLE [dbo].[Student]
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Name] [char](50) NULL
    )

Now as soon as the table is created, its event data will be stored in AuditChanges. Query the Audit table to get the result. As seen below, the Audit table has successfully captured the Student dbo creation details.