This article explores database DDL triggers for auditing Data Definition Language (DDL) on Azure SQL Database.
Database audit is critical and essential for securing your database infrastructure. Azure SQL Database is a PaaS database solution for SQL Server. It provides an in-built server and database level audit that you can enable and configure for your requirements. However, DDL triggers also play an essential role in the audit. Suppose you have a critical Azure production database and you want to log each CREATE, ALTER, DROP table statements for that database. Similarly, in another case, you do not want any user to DROP the table if it is not a member of the db_owner role. In this case, SQL Server DDL triggers play a vital role to control unauthorized access to the database. You can use these triggers to do the following tasks.
- Prevent specific changes to the database schema
- Audit changes in the database schema
The DDL triggers works on the DDL events. These events correspond to the T-SQL having the CREATE, ALTER, DROP, GRANT, DENY, REVOKE or the UPDATE STATISTICS.
The following figure represents an example of a DDL trigger once a specific event is triggered.
Usually, in a typical database without any auditing mechanism in a controlled and secure database environment, it is challenging to answer suspicious actions without an audit. As a DBA, you need to answer your management and find out answers to the questions:
- Who dropped the table?
- When is the table dropped?
EVENTDATA() built-in functions
The DDL triggers capture the details for a DDL trigger using the EVENTDATA() function in an XML format. The XML includes the following information:
- Event timestamp
- SPID of the connection in which the executed query fires the DDL trigger
- Event details
It returns the following data in the XML:
|
XML column |
Details |
|
EventType |
It gives the type of event that caused the DDL trigger. For example, CREATE, ALTER, DROP. |
|
PostTime |
Event timestamp |
|
SPID |
Session ID |
|
ServerName |
SQL Server instance name |
|
LoginName |
It is the login details for the SPID |
|
UserName |
|
|
DatabaseName |
It is the database name in which the DDL trigger is fired. |
|
ObjectName |
Object name such as table name, schema name |
|
ObjectType |
Type of the object such as Table, View, Stored procedure. |
|
TSQLCommand |
The Eventdata captures the TSQL command fired the DDL trigger |
|
SetOptions |
SET options used in the connection |
|
CommandText |
It gives the create, alter or drop command. |
To capture the event data using a DDL trigger, let’s create a table with the following script in the Azure SQL Database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE dbo.CaptureDDLEvents ( EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, EventType NVARCHAR(100), EventDDL NVARCHAR(MAX), EventXML XML, DatabaseName NVARCHAR(255), SchemaName NVARCHAR(255), ObjectName NVARCHAR(255), HostName VARCHAR(64), IPAddress VARCHAR(48), ProgramName NVARCHAR(255), LoginName NVARCHAR(255) ); |
The following script creates a DDL trigger on the Azure SQL Database for CREATE TABLE, ALTER TABLE, DROP TABLE statements. The trigger uses EVENTDATA() function , and inserts data into [CaptureDDLEvents] table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
CREATE TRIGGER AzureSQL_CAD_Trigger ON DATABASE FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE AS BEGIN SET NOCOUNT ON; DECLARE @EventData XML = EVENTDATA(); DECLARE @ip varchar(48) = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address')); INSERT Azuredemodatabase.dbo.CaptureDDLEvents ( EventType, EventDDL, EventXML, DatabaseName, SchemaName, ObjectName, HostName, IPAddress, ProgramName, LoginName ) SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'), @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'), @EventData, DB_NAME(), @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'), @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME(); END GO |
To test the DDL trigger, let’s create a new table in the Azure SQL Database. It should fire the DDL trigger and insert captured data into the [CaptureDDLEvents] table.
Now, check the record in the [CaptureDDLEvents] table, and you get captured data in an XML format.
Click on the hyperlink in the [EventXML] column, and it gives details as shown below.
Let’s analyze the captured data from the above screenshot.
|
XML column |
Captured data |
Description |
|
EventType |
CREATE_TABLE |
For CREATE TABLE statement, it gives the CREATE_TABLE event. |
|
PostTime |
2021-03-05T11:32:43.897 |
It is the event timestamp |
|
SPID |
58 |
It is the SPID under which the CREATE TABLE statement was executed. |
|
ServerName |
Azuredemosqldemo |
It is my Azure SQL Database logical server name. |
|
LoginName |
Sqladmin |
It is the SQL login name through which we authenticated to Azure SQL Database. |
|
DatabaseName |
Azuredemodatabase |
It is the Azure SQL Database name. |
|
SchemaName |
Dbo |
It is the database schema name. |
|
ObjectName |
MyDemoTable |
It is the SQL table name that we created using the CREATE TABLE statement. |
|
ObjectType |
TABLE |
The object type refers to which object is affected, for example, table, procedure. |
|
TSQLCommand |
T-SQL script |
The TSQL command refers to the SQL script along with the SET options. This column helps track the script that the user used for object creation, removal or modification. |
We have configured DDL triggers for both CREATE and DROP table statements. Therefore, let’s initiate a DROP TABLE transaction and capture the details. We can verify that the user sqladmin dropped the table [MyDemoTable] from the [AzureDemoDatabase] Azure SQL Database.
As we saw earlier, the DDL triggers can help audit the events for a database activity and figure out the answers – Who did it? When did it happen? Which script was executed?
But what if we want to restrict certain operations. For example, suppose we do not want any user to drop the table. In this DDL trigger, we specify a message for the user and rollback the transaction.
|
1 2 3 4 5 6 |
CREATE TRIGGER DDL_DoNotDropSQLTable ON DATABASE FOR DROP_TABLE AS PRINT 'Dropping a table is not allowed in the [AzureDemoDatabase] Azure SQL database' ROLLBACK |
To validate the DDL trigger functionality, try dropping an existing table. You get an error message as we specified in the trigger body. The user has the db_owner permission to drop the table, but the DDL trigger safeguarded you.
In this case, if we want to drop the table, first disable the DDL trigger, drop the table and enable it again.
|
1 2 3 4 5 6 7 8 |
DISABLE TRIGGER DDL_DoNotDropSQLTable ON DATABASE GO DROP TABLE student GO ENABLE TRIGGER PreventDropTable ON DATABASE GO |
In a typical database environment, multiple users connect to your database. In the above case, everyone is blocked from dropping the table from the Azure database. However, in an ideal scenario, we do not want to restrict a person with db_owner permission. In the following script, we implement a DDL trigger that checks whether the user is a member of the db_owner permission group. In case the user does not have a db_owner group, it prints a message and rollbacks the transaction. However, a transaction for the user with db_owner permission does not enter the IF block and successfully drops the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TRIGGER DDL_DoNotDropDBTable ON DATABASE FOR DROP_TABLE AS BEGIN IF IS_MEMBER ('db_owner') = 0 BEGIN PRINT 'Dropping a table is not allowed in the [AzureDemoDatabase] Azure SQL database' ROLLBACK TRANSACTION; END END |
To validate our DDL trigger logic, let’s create a SQL login in the Azure SQL Database and provide db_datareader, db_datawriter, db_ddladmin permissions. The user in the db_ddladmin permission group can create, drop and alter objects in the SQL Database.
|
1 2 3 4 5 6 7 8 9 |
CREATE LOGIN appdataread WITH PASSWORD = 'P@ssw0rd5' GO CREATE USER appdataread FROM LOGIN appdataread; ALTER ROLE db_datareader ADD MEMBER appdataread; ALTER ROLE db_datawriter ADD MEMBER appdataread; ALTER ROLE db_ddladmin ADD MEMBER appdataread; |
Connect to the Azure SQL DB with [appdataread] login credentials.
In the option, specify the Azure SQL DB name as shown below.
Once connected, drop an existing table. It does not allow us to drop it and gives a message embedded in the DDL trigger body. The user does not have db_owner permission. Therefore, he cannot drop the table even though it holds db_ddladmin permission.
Now, try to drop the table with the user having db_owner permission.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT USER_NAME() AS UserName GO DECLARE @i int set @i= IS_MEMBER ('db_owner') SELECT CASE when @i=1 then 'Yes' else 'No' end as DBOwner go DROP TABLE dbo.t2 |
The following figure shows that the user has db_owner permission in the Azure SQL Database. Therefore, the DDL trigger does not roll back the transaction, and it successfully drops the table.
Conclusion
This article explored the DDL triggers on Azure SQL Database for auditing and preventing users from doing specific actions such as DROP TABLE. It is advisable to configure a database-level audit for a critical production database to safeguard your database from unauthorized activities.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023