Post
Ask Question

Customized Auditing In SQL For DDL Operations At Server Level

Introduction

In this article, we will learn how to implement the Audit feature in the SQL server. This includes storing all the Audit information in a local table with respect to operations such as creating, altering, and dropping 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 the 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,

  1. Creation of Trigger: We need to create a trigger for all DDL activities such as Create, Alter, and Drop operations at the server level.
  2. 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.
  3. Creation of a local Audit table: We will be using a local table to store Audit data from the data returned by the EventData function.
  4. Creation of Stored procedure: A simple stored procedure is required to capture audit data into an XML parameter using the EventData function and store the same data in 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 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 the server level which will store the event data as well as the information in the AuditChanges table. Here [1] refers to the top 1 and datatype varchar means we are storing it in the 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 the AuditChanges table or not. Below is the query to create the table student.

CREATE TABLE [dbo].[Student]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
      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.

Output

Conclusion

We have achieved the auditing feature locally by performing the task very easily.


Similar Articles