Rajendra Gupta
DDL Trigger Flow

DDL triggers in Azure SQL Database

July 28, 2021 by

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.

DDL Trigger Flow

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



It gives the type of event that caused the DDL trigger. For example, CREATE, ALTER, DROP.


Event timestamp


Session ID


SQL Server instance name


It is the login details for the SPID



It is the database name in which the DDL trigger is fired.


Object name such as table name, schema name


Type of the object such as Table, View, Stored procedure.


The Eventdata captures the TSQL command fired the DDL trigger


SET options used in the connection


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.

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.

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.

Create a table

Now, check the record in the [CaptureDDLEvents] table, and you get captured data in an XML format.

EVENTDATA() built-in functions

Click on the hyperlink in the [EventXML] column, and it gives details as shown below.

View XML data

Let’s analyze the captured data from the above screenshot.

XML column

Captured data




For CREATE TABLE statement, it gives the CREATE_TABLE event.



It is the event timestamp



It is the SPID under which the CREATE TABLE statement was executed.



It is my Azure SQL Database logical server name.



It is the SQL login name through which we authenticated to Azure SQL Database.



It is the Azure SQL Database name.



It is the database schema name.



It is the SQL table name that we created using the CREATE TABLE statement.



The object type refers to which object is affected, for example, table, procedure.


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.

DROP TABLE transaction

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.

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.

The transaction triggger

In this case, if we want to drop the table, first disable the DDL trigger, drop the table and enable it again.

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.

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.

Connect to the Azure SQL DB with [appdataread] login credentials.

SSMS connection

In the option, specify the Azure SQL DB name as shown below.

Connect to Azure SQL Database

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.

Drop an existing table

Now, try to drop the table with the user having db_owner permission.

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.

User with db owner permissions


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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)