By: Muhammad Kamran | Updated: 2024-03-18 | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Auditing and Compliance
Problem
One requirement in my organization is to audit SQL Server DDL (Create, Alter and Drop) commands and store the audited information in a table to easily query the table to get the required data and generate reports whenever necessary. How can I capture these commands in SQL Server?
Solution
There are different methods to audit SQL Server DDL statements, and the most common are SQL Server audits, SQL trace, and DDL triggers. Each type has pros and cons according to the requirement or situation faced. In our scenario, we will choose a DDL trigger to audit DDL events on the database and tables, and we will store the audited data in a table to retrieve the data easily.
What are SQL Server DDL Triggers?
DDL triggers are the same as other triggers. The main difference is the event for which the trigger will execute (DDL statements) and perform the action described in the trigger body. We can use DDL triggers to get tasks done in response to a DDL event, like preventing an unauthorized change to the database, executing any specific action, or recording modifications completed in the database. In this tip, we will be working on the later action, and we will also use the database mail profile to send an email alert whenever a DDL statement is executed.
Major Steps
Below are the major steps we will perform to complete this task:
- Create a new table that will hold the audit data.
- Create a DDL trigger that will execute in the event of DDL statements.
- Execute the Alter statement on the database and table to test the newly created trigger code.
Let's start with the steps mentioned above.
Step 1: Create New Table
We need to create a new table to hold the data we get from executing the DDL trigger. For this example, we will create the table in the MSDB system database. You can choose any other database as well. Below is the script for the table creation. Please note that we define the columns in the new table according to our needs. You can create a table with fewer or more columns according to your requirements.
USE [msdb] GO CREATE TABLE [dbo].[TracK_DDL]( [Track_id] [int] IDENTITY(1,1) NOT NULL, [EventType] [varchar](100) NULL, [EventTime] [datetime]NULL, [ServerName] [varchar](100) NULL, [WhoDidIt] [varchar](100) NULL, [TSQL_Text] [varchar](4000) NULL ) ON [PRIMARY] GO
Step 2: Create a DDL Trigger
Next, we need to create the DDL trigger, which will execute when DDL statements run against a database or table. The trigger code is divided into different sections for better understanding. Each section will be discussed further in this tip. The complete code for creating a DDL trigger is as follows.
------------------------- Section 1 ----------------------------
--------------- Declare Trigger type & Event type --------------
USE [master]
GO
CREATE TRIGGER [Trg_Track_DDL]
ON ALL SERVER
FOR DDL_DATABASE_EVENTS, DDL_TABLE_EVENTS -- Trigger will fire for Create, Alter, and Drop statements on Databases and Tables
AS
begin
------------------------- Section 2 ----------------------------
--------- Variable declaration & populating with values --------
DECLARE @data xml,
@EventType varchar(100),
@EventTime datetime,
@ServerName varchar(100),
@AffectedLoginName varchar(100),
@WhoDidIt varchar(100),
@EmailSubject varchar(500),
@EmailBody varchar(800),
@EmailRecipients varchar(300),
@TSQL varchar(4000)
SET @EmailRecipients = '[email protected]'
SET @data = EVENTDATA()
SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
SET @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
SET @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
SET @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
SET @TSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(4000)')
------------------------- Section 3 ----------------------------
-------------------- The Email Body Code -----------------------
SET @EmailSubject = @EventType + ' occured by ' + @WhoDidIt + ' on Server' + @ServerName +
' occured at: ' + convert(Varchar, @EventTime)
SET @EmailBody = '<H5 style=" color: red; " >' +@EventType + ' </H5> Statement executed by <H5 style=" color: red; " >' + @WhoDidIt + ' </H5> on Server <b>' +
@ServerName + '</b> at: <b>' + convert(Varchar, @EventTime) +
'</b> and below is the T-SQL executed <H5 style=" color: red; " > {{ ' +@TSQL +' }} </H5>'
------------------------- Section 4 ---------------------
------------------ Insert Data in table -----------------
Insert into [msdb].[dbo].[TracK_DDL]
(
EventType,
EventTime,
ServerName,
WhoDidIt,
TSQL_Text
)
Values
(
@EventType,
@EventTime,
@ServerName,
@WhoDidIt,
@TSQL
);
------------------------- Section 5 -------------------------
-----------------Email Notification code --------------------
EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailRecipients
, @subject = @EmailSubject
, @body = @EmailBody
, @importance = 'High'
, @profile_name = 'Test-MailProfile' -- Put DB Mail profile name here
, @body_format = 'HTML' ;
END
GO
ENABLE TRIGGER [Trg_Track_DDL] ON ALL SERVER
GO
As mentioned, the above code is the complete DDL trigger code. To better understand the code, we will break it down further and cover each section of the code.
Section 1 Trigger Code
Let's discuss the first section of the above code.
------------------------- Section 1 ---------------------------- --------------- Declare Trigger type & Event type -------------- USE [master] GO CREATE TRIGGER [Trg_Track_DDL] ON ALL SERVER FOR DDL_DATABASE_EVENTS, DDL_TABLE_EVENTS -- Trigger will fire for Create, Alter, and Drop statements on Databases and Tables
This section shows the creation of a trigger with the name Trg_Track_DDL. The next line in the code ON ALL SERVER means to create the trigger on the server level. We can also create the trigger on the server and database levels. The trigger created on the server level will be executed for any DDL statement run against any database or table, irrespective of a particular database name.
On the other hand, if we use ON DATABASE instead of ON ALL SERVER, the trigger will be created as a database trigger (e.g., a trigger created for a test database). It will execute only if any triggering events occur on that particular database (test database). But if we are creating a trigger on the database level (ON DATABASE), we also need to change the triggering event (mentioned in the next line in the above code) because the database-level triggering events are different than the server level.
The next line of code, FOR DDL_DATABASE_EVENTS, DDL_TABLE_EVENTS, defines the DDL events that will cause the trigger to execute. These two DDL events include Create, Alter, and Drop statements against all tables and all databases.
Section 2 Trigger Code
Now, let's discuss Section 2 of the main code.
------------------------- Section 2 ----------------------------
--------- Variable declaration & populating with values --------
DECLARE @data xml,
@EventType varchar(100),
@EventTime datetime,
@ServerName varchar(100),
@AffectedLoginName varchar(100),
@WhoDidIt varchar(100),
@EmailSubject varchar(500),
@EmailBody varchar(800),
@EmailRecipients varchar(300),
@TSQL varchar(4000)
SET @EmailRecipients = '[email protected]'
SET @data = EVENTDATA()
SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
SET @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
SET @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
SET @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
SET @TSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(4000)')
In the above code snippet, multiple variables with proper data types are declared, which will hold different event values. All these variables are populated with related data starting with the 'SET' keyword. Using the SQL Server built-in function called EVENTDATA, this function returns all the data about the event that has occurred (for example, an Alter Database statement). We will only collect a few important information from that EVENTDATA built-in function, as mentioned in the last few lines of the above code. We are collecting only the type of the event, the time of the event, the DB Server name, the login name, and the TSQL statement that has fired the trigger.
Section 3 Trigger Code Explanation
The following snippet from the trigger creation code (Section 3) is mentioned below.
------------------------- Section 3 ----------------------------
-------------------- The Email Body Code -----------------------
SET @EmailSubject = @EventType + ' occured by ' + @WhoDidIt + ' on Server' + @ServerName +
' occured at: ' + convert(Varchar, @EventTime)
SET @EmailBody = '<H5 style=" color: red; " >' +@EventType + ' </H5> Statement executed by <H5 style=" color: red; " >' + @WhoDidIt + ' </H5> on Server <b>' +
@ServerName + '</b> at: <b>' + convert(Varchar, @EventTime) +
'</b> and below is the T-SQL executed <H5 style=" color: red; " > {{ ' +@TSQL +' }} </H5>'
In section 3, we have combined variables (holding event information) with the text we want to see in the email alert (shown in the above code) and then assigned all the combined information to another variable, @EmailSubject and @EmailBody. We will use the information stored in these two variables in the last section of the main code (Section 5), where we will send an email alert about the event that occurred (e.g., an Alter Database statement).
Section 4 Trigger Code
Let's move to the next part of the trigger code.
------------------------- Section 4 ---------------------
------------------ Insert Data in table -----------------
Insert into [msdb].[dbo].[TracK_DDL]
(
EventType,
EventTime,
ServerName,
WhoDidIt,
TSQL_Text
)
Values
(
@EventType,
@EventTime,
@ServerName,
@WhoDidIt,
@TSQL
);
The above code is pretty straightforward. The information collected and temporarily stored in the variables (in section 2) is inserted into the table we created initially. Every time the trigger is fired due to some DDL statements, that event information will be stored in the table mentioned above.
Section 5 Trigger Code
At this point, we have collected the event data and stored it in the table. We can also send the event data as an email alert to specific recipients, as shown below in Section 5 of the main code.
------------------------- Section 5 -------------------------
-----------------Email Notification code --------------------
EXEC msdb.dbo.sp_send_dbmail
@recipients = @EmailRecipients
, @subject = @EmailSubject
, @body = @EmailBody
, @importance = 'High'
, @profile_name = 'Test-MailProfile' -- Put DB Mail profile name here
, @body_format = 'HTML';
END
GO
ENABLE TRIGGER [Trg_Track_DDL] ON ALL SERVER
GO
The above code will send the event data in HTML format in an email to the recipients mentioned in the @EmailRecipients variable. You can also mention the email account ID of the recipients in place of @EmailRecipients separated with a semicolon (;). For the above code to execute successfully, the database mail profile should be enabled and already configured, in our case, Test-MailProfile. You have to replace the value of the @profile_name with the DB mail profile configured in your SQL environment.
The second to last line simply enables the trigger. After executing the DDL trigger code, the trigger will be created successfully. You can find the trigger in the SQL Server Studio Management using the steps below.
- Open SSMS and connect to SQL Server Instance.
- Expand Server Objects.
- Expand Triggers, and you will find the trigger you created.
Step 3: Test the Trigger Code
Now, it is time to test the functionality of the trigger. As you know, the trigger is created based on the DDL events on the tables and databases. Whenever any DDL statement is executed on the table or database, the trigger will fire and execute the trigger code.
Let's execute a simple DDL statement against the database named test to modify its recovery model to Simple, as shown below.
USE [master] GO ALTER DATABASE [Test] SET RECOVERY Simple WITH NO_WAIT GO
When the above DDL statement is executed, the trigger will fire, and the trigger code will execute. We mentioned the email notification code in Section 5 of the trigger body, which sends an email alert with the details of the DDL statement executed. Below is the email I received.
You can add or remove email alert details as necessary by changing the code in Section 3 of the trigger body.
Section 4 of the trigger body includes an Insert statement, which will record (in the Track_DDL table) the details of the DDL statement run against the table or database. Execute a simple Select query against the table Track_DDL to return the data stored in the table after the DDL statement. As you can see from the screenshot below, the details of the DDL statement are audited in the Track_DDL table.
Next Steps
- This tip shared a simple method to implement a DDL trigger in your SQL Server environment and audit different events.
- You can modify the DDL trigger code above and make changes according to your requirements.
About the author
Muhammad Kamran is a Senior SQL DBA with many years of experience in providing SQL Server high-availability solutions. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-03-18
