SQL Server Database Configuration Change Alert with DDL Triggers

Problem

There are often times when a database change has been made and no one knows who made the change, when it was made, and what the change was. In this article, we look at how to capture these details.

Solution

In our organization, we have a weekly infrastructure review process. Recently, during our review process, we found that one of the production database’s recovery models was changed from FULL recovery model to SIMPLE recovery model. The change was risky because we couldn’t perform point-in-time recovery in case of a disaster, which would lead to major issues.

To avoid such issues, we decided to create a DDL trigger. The DDL trigger will be fired when anyone executes the ALTER DATABASE statement. Along with an alert, we want to email the following details to help us find more information:

1. The command and exact ALTER DATABASE statement executed to change the database configuration.
2. The user/login used to run the ALTER DATABASE statement.
3. The name of the database on which the ALTER DATABASE statement was executed.

To achieve this, we will use DDL triggers.

What are DDL triggers?

DDL triggers are executed when various DDL events occur, such as CREATE, ALTER, and DROP database objects on a database server. It can be created for server-specific events like CREATE DATABASE, ALTER DATABASE, etc.

Here are a few notes that might be useful to understand the concept:

1. Unlike traditional T-SQL triggers, DDL triggers do not create inserted or deleted tables.
2. The DDL triggers cannot be used as INSTEAD OF triggers.
3. The scope of the trigger is based on the event for which the trigger is created. It can be server-scoped or database-scoped.
4. The DDL trigger stores the captured information in an XML file. The details can be extracted using the EVENTDATA() function of SQL Server. You can read this article to learn more about the EVENTDATA() function.

For more details on DDL triggers, you can read Microsoft’s official document on DDL Triggers.

You can view the list of triggers by executing the following T-SQL query:

use master
go
select name [Trigger Name], object_id, parent_class_desc , type_desc , create_date from sys.server_triggers

Here is a screenshot of the query output:

You can view the trigger in SQL Server Management Studio (SSMS). Open SSMS, connect to the SQL Server instance, and expand Server Objects and Triggers, as seen in the image below.

T-SQL Script to Create DDL Trigger

Let’s review the DLL trigger code to generate an email alert/notification.

For understanding, I have broken the code of the trigger into three parts.

Part 1 – The first code block is creating a temporary table named #criticalAlert. The table contains four columns, which include the following:

1. Command: The column contains the command name. e.g., ALTER DATABASE
2. SQLQuery: The column contains the T-SQL query used against a database.
3. ExecutedBy: User/SQL Login to execute the query.
4. DBName: The column contains the database name on which the ALTER DATABASE statement was executed.

The T-SQL code to create the table is below:

CREATE table #CriticalAlert (Command varchar(50),SQLQuery varchar(5000),ExecutedBy varchar(100),DatabaseName varchar(500))

Part 2 – The second section contains the INSERT INTO SELECT statement. The query extracts the DDL trigger event data using the EVENTDATA() function:

INSERT into #CriticalAlert
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)') ,
       EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  ,
       EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)')  ,
       EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(500)')  

Part 3 – The third section contains a T-SQL code used to email the details. It uses a dynamic SQL query that contains appropriate HTML table tags to show the details in tabular format. We are using SQL Server’s Database Mail feature. Check out these tips to learn more about Database Mail.

DECLARE @subject NVARCHAR(max)
DECLARE @tableHTML NVARCHAR(max)
 
SET @subject = 'Critical Alert: ALTER DATABASE statement has been executed on : ' + @@servername
SET @tableHTML =
  '     <html><Body><style type="text/css">table {font-size:9.0pt;font-family:verdana;text-align:left;}tr {text-align:left;} 
  h3 {display: block;font-size: 15.0pt;font-weight: bold; font-family: verdana;        text-align:left;       } </style><H3>Critical Alert: Database configuration has been changed </H3>' + N'<table border="1">'
  +N'<tr><th>Command</th><th>SQL Query</th><th>Command Executed By</th><th>Database Name</th></tr>'
                 + Cast((SELECT 
         Command AS 'TD', '', 
         SQLQuery AS 'TD', '',
         ExecutedBy AS 'TD', '', 
         DatabaseName AS 'TD', '' 
         FROM #CriticalAlert FOR xml path ( 'tr' ), root) AS NVARCHAR(max))
                 + N'</table>      </html>     </Body>'
EXEC msdb..sp_send_dbmail
  @profile_name = 'Database_Mail_Profile',
  @recipients = Youremail@outlook.com',
  @subject = @subject,
  @importance = 'High',
  @body = @tableHTML,
  @body_format = 'HTML';  

The entire code to create a trigger is as follows:

CREATE TRIGGER audit_db_configuration_change
ON ALL SERVER   
FOR ALTER_DATABASE   
AS   
CREATE table #CriticalAlert (Command varchar(50),SQLQuery varchar(5000),ExecutedBy varchar(100),DatabaseName varchar(500))
INSERT into #CriticalAlert
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') ,
       EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  ,
       EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)')  ,
       EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(500)')  
  
DECLARE @subject NVARCHAR(max)
DECLARE @tableHTML NVARCHAR(max)
 
SET @subject = 'Critical Alert: ALTER DATABASE statement has been executed on : ' + @@servername
SET @tableHTML =
  '     <html><Body><style type="text/css">table {font-size:9.0pt;font-family:verdana;text-align:left;}tr {text-align:left;} 
  h3 {display: block;font-size: 15.0pt;font-weight: bold; font-family: verdana;        text-align:left;       } </style><H3>Critical Alert: Database configuration has been changed </H3>' + N'<table border="1">'
  +N'<tr><th>Command</th><th>SQL Query</th><th>Command Executed By</th><th>Database Name</th></tr>'
                 + Cast((SELECT 
         Command AS 'TD', '', 
         SQLQuery AS 'TD', '',
         ExecutedBy AS 'TD', '', 
         DatabaseName AS 'TD', '' 
         FROM #CriticalAlert FOR xml path ( 'tr' ), root) AS NVARCHAR(max))
                 + N'</table>      </html>     </Body>'
EXEC msdb..sp_send_dbmail
  @profile_name = 'Database_Mail_Profile',
  @recipients = 'niralishastri89@outlook.com',
  @subject = @subject,
  @importance = 'High',
  @body = @tableHTML,
  @body_format = 'HTML';  
Drop table #CriticalAlert
GO  

Test the DDL Trigger

To test the DDL trigger, I will change the recovery model of the EltechDB database from SIMPLE recovery model to FULL recovery model. To do that, execute the following SQL query:

use master
go
alter database [EltechDB] set recovery full

Once the command executes successfully, you will receive an email that looks like the following image:

As you can see, the alert has sent all the details useful for tracking what was changed and who made the configuration change.

Next Steps

• Read Generate HTML Formatted Emails from SQL Server to learn how to create HTML formatted email using T-SQL script.
• Read Setup SQL Server Database Mail to use a Gmail, Hotmail, or Outlook account to learn more about SQL Server database mail and how to configure it.