DDL triggers in Azure SQL Database
This article discusses database DDL triggers for auditing Data Definition Language (DDL) on Azure SQL Database.
For securing your database infrastructure, a database audit is vital. Azure SQL Database is a SQL Server PaaS database solution. It has a built-in server and database audit that you can enable and adjust to meet your needs. DDL triggers, on the other hand, are critical in the audit. Assume you have a mission-critical Azure production database for which you want to log all CREATE, ALTER, and DROP table statements. In another situation, you don’t want any user who isn’t a member of the db owner role to DROP the table. In this scenario, SQL Server DDL triggers are critical in preventing unauthorized database access. Can use these triggers to complete the following tasks.
- Prevent specific database schema changes.
- Should audit changes to the database schema.
DDL triggers operate on DDL events. These events correspond to the CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS T-SQL statements.
It is difficult to respond to suspicious actions in a conventional database without an auditing tool in a controlled and secure database environment. As a DBA, you must respond to management and find solutions to the following questions:
- Who threw the table away?
- When will the table be lowered?
EVENTDATA() built-in functions
The EVENTDATA() function in the DDL triggers captures the details for a DDL trigger in an XML format. The following data is included in the XML:
- Event timestamp SPID of the connection in which the performed query triggers the DDL trigger
- Information about the event
In XML, it returns the following information:
| 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. |
Let’s construct a table in the Azure SQL Database with the following script to capture the event data using a DDL trigger.
|
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 provides a DDL trigger for CREATE TABLE, ALTER TABLE, and DROP TABLE statements on the Azure SQL Database. The trigger inserts data into the [CaptureDDLEvents] table using the EVENTDATA() function.
|
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
|
Let’s construct a new table in the Azure SQL Database to test the DDL trigger. The DDL trigger should be fired and insert the captured data into the [CaptureDDLEvents] table.
Then look for the entry in the [CaptureDDLEvents] table to receive the collected data in XML format.
The [EventXML] column has a hyperlink that leads to more information.
Let’s take a look at the information we’ve gathered.
| 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. |
DDL triggers have been set up for both CREATE and DROP table statements. As a result, let’s run a DROP TABLE transaction and record the information. As we can see, the user sqladmin deleted the table [MyDemoTable] from the [AzureDemoDatabase] Azure SQL Database.
As we saw previously, DDL triggers can assist in auditing database activity events and determining the answers to the questions – Who did it? When did it take place? Which script was run?
But what if we wish to limit the operations we can perform? For instance, let’s say we don’t want anyone to drop the table. We specify a message for the user and rollback the transaction in this DDL trigger.
|
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
|
Try dropping an existing table to test the DDL trigger functionality. As described in the trigger body, you receive an error notice. The user has the db owner permission to drop the table, but the DDL trigger protected you.
If we wish to drop the table in this situation, we must first disable the DDL trigger, then 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
|
Multiple users connect to your database in a standard database setting. In the example above, no one is allowed to delete the table from the Azure database. However, in an ideal situation, we would not want to restrict someone who has the db owner access. We create a DDL trigger in the script below that checks if the user belongs to the db owner permission group. It prints a notification and rolls back the transaction if the user does not belong to the db owner group. A trade for the user with the db owner permission, on the other hand, skips the IF block and drops the table successfully.
|
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 test our DDL trigger logic, let’s create a SQL login in the Azure SQL Database and give it the db datareader, db datawriter, and db ddladmin rights. The db ddladmin permission group allows users to create, delete, and modify items 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;
|
Use [appdataread] login credentials to connect to the Azure SQL DB.
As shown below, in the choice, type the name of the Azure SQL DB.
Drop any existing tables once you’ve been joined. It won’t let us drop it and instead displays a message within the DDL trigger body. Db owner permission isn’t granted to this user. As a result, despite having db ddladmin permission, he cannot drop the table.
Now, try dropping the table with the user who has 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
|
In the Azure SQL Database, the user has the db owner’s permission. As a result, the DDL trigger does not roll back the transaction, and the table is successfully dropped.
Conclusion
This post looked at Azure SQL Database DDL triggers for auditing and restricting users from doing particular operations like DROP TABLE. It’s good to set up a database-level audit for a crucial production database to protect it from unauthorized access.
Enteros
About Enteros
IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
The Role of Intelligent Database Analytics in Optimizing Hybrid Cloud Performance
- 27 July 2026
- Database Performance Management
Introduction Hybrid cloud has become the preferred IT strategy for modern enterprises seeking the flexibility of public cloud services while maintaining control over critical workloads in private cloud and on-premises environments. Organizations across banking, healthcare, retail, manufacturing, telecommunications, and government sectors increasingly rely on hybrid cloud architectures to balance scalability, compliance, cost efficiency, and application … Continue reading “The Role of Intelligent Database Analytics in Optimizing Hybrid Cloud Performance”
How to Optimize Aerospace and Defense Operations with Enteros Database Software, Database Observability, and AI-Powered Analytics
- 26 July 2026
- Database Performance Management
Introduction The aerospace and defense (A&D) industry operates some of the world’s most sophisticated and mission-critical technology environments. Aircraft manufacturers, defense contractors, space organizations, satellite operators, maintenance providers, and government defense agencies manage enormous volumes of engineering, manufacturing, logistics, operational, and security data every day. From aircraft design and production to fleet maintenance, mission planning, … Continue reading “How to Optimize Aerospace and Defense Operations with Enteros Database Software, Database Observability, and AI-Powered Analytics”
How to Optimize Automotive Manufacturing with Enteros Database Software, AI-Powered Analytics, and Operational Intelligence
Introduction The automotive industry is undergoing its most significant transformation in decades. Traditional vehicle manufacturing is rapidly evolving with electric vehicles (EVs), autonomous driving technologies, connected cars, smart factories, robotics, Industrial Internet of Things (IIoT), artificial intelligence (AI), and cloud computing. Automotive manufacturers and suppliers must manage increasingly complex operations while maintaining production efficiency, product … Continue reading “How to Optimize Automotive Manufacturing with Enteros Database Software, AI-Powered Analytics, and Operational Intelligence”
How AI-Powered Database Observability Strengthens Enterprise Cyber Resilience
Introduction Cyberattacks have evolved dramatically over the past decade. Modern enterprises face increasingly sophisticated ransomware attacks, insider threats, supply chain compromises, credential theft, and advanced persistent threats (APTs). As organizations continue adopting cloud-native architectures, hybrid environments, and distributed applications, protecting enterprise databases has become one of the highest cybersecurity priorities. Databases store an organization’s most … Continue reading “How AI-Powered Database Observability Strengthens Enterprise Cyber Resilience”