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
How Can Enterprises Reduce Database Costs Without Sacrificing Performance?
- 11 September 2026
- Database Performance Management
Enterprises can reduce database costs without sacrificing performance by identifying inefficient SQL, right-sizing infrastructure, eliminating unused capacity, analyzing workload patterns, improving storage efficiency, and using database observability to connect resource consumption with application performance. Database cost optimization helps organizations control infrastructure spending while Enteros UpBeat provides performance intelligence, workload visibility, predictive analytics, and Cloud FinOps … Continue reading “How Can Enterprises Reduce Database Costs Without Sacrificing Performance?”
How Can AI Detect Database Performance Anomalies Before They Cause Downtime?
AI can detect database performance anomalies by continuously analyzing workload patterns, SQL behavior, latency, resource usage, waits, and historical baselines. AI database anomaly detection helps identify unusual behavior before it becomes a larger outage. With Enteros UpBeat, IT teams can detect emerging issues earlier, investigate root causes faster, and improve database reliability across complex enterprise … Continue reading “How Can AI Detect Database Performance Anomalies Before They Cause Downtime?”
How Can Financial Institutions Detect Database Bottlenecks Before Transaction Delays Occur?
- 10 September 2026
- Database Performance Management
Financial institutions can detect database bottlenecks before transaction delays occur by continuously monitoring query latency, database waits, CPU, memory, I/O, locking, workload changes, and transaction throughput. By combining performance baselines, anomaly detection, SQL intelligence, predictive analytics, and automated root cause analysis, teams can identify emerging issues early and optimize databases before customer-facing banking, payment, or … Continue reading “How Can Financial Institutions Detect Database Bottlenecks Before Transaction Delays Occur?”
What Role Does AI-Powered Database Monitoring for Banking Play in Performance Management?
AI-powered database monitoring for banking helps financial institutions detect anomalies, identify performance bottlenecks, analyse SQL workloads, predict capacity risks, and accelerate troubleshooting. By combining database observability with automated analysis, AI-powered database performance supports faster incident response, more reliable transactions, better resource utilisation, stronger capacity planning, and smarter cloud cost decisions across increasingly complex banking database … Continue reading “What Role Does AI-Powered Database Monitoring for Banking Play in Performance Management?”