Customized Auditing In SQL For DDL Operations At Server Level

Introduction

 
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,
  1. Creation of Trigger
    We need to create a trigger for all DDL activities such as Create, Alter and Drop operations at 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 EventData function.

  4. 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,
  1. CREATE TABLE [dbo].[AuditChanges]  
  2. (  
  3.     [DatabaseName] [nchar](50) NULL,  
  4.     [TableName] [nchar](50) NULL,  
  5.     [EventType] [nchar](50) NULL,  
  6.     [LoginName] [nchar](50) NULL,  
  7.     [SchemaName] [nchar](50) NULL,  
  8.     [SQLCommand] [nchar](50) NULL,  
  9.     [CaptureTime] [nchar](50) NULL  
  10. )   
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.
  1. ALTER Trigger Audit_Trigger  
  2. ON ALL SERVER  
  3. FOR Create_Table, Alter_Table, Drop_Table  
  4. As Begin  
  5. DECLARE @EventData XML  
  6. SELECT @EventData = EVENTDATA()  
  7. INSERT INTO Test.dbo.AuditChanges  
  8. (DatabaseName, TableName, EventType, LoginName, SchemaName, SQLCommand, CaptureTime)  
  9. Values   
  10. (  
  11.    @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(50)'),  
  12.    @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(50)'),  
  13.    @EventData.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),  
  14.    @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)'),  
  15.    @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(50)'),  
  16.    @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(50)'),  
  17.    GETDATE()  
  18. )  
  19. 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.
  1. CREATE TABLE [dbo].[Student]  
  2. (  
  3.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  4.     [Name] [char](50) NULL  
  5. )  
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.
 
 
 

Conclusion

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