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 to Optimize Media and Entertainment Database Performance with Enteros Database Software, AI-Powered Analytics, and Database Observability
- 30 August 2026
- Database Performance Management
Introduction The media and entertainment industry has become a digital-first business. Streaming services, digital publishing, online gaming content, advertising platforms, social experiences, content management systems, subscription platforms, and personalized recommendations all depend on databases. Modern media companies process enormous volumes of customer, content, advertising, transaction, and behavioral information. Read more”Indian Country” highlights Enteros and its … Continue reading “How to Optimize Media and Entertainment Database Performance with Enteros Database Software, AI-Powered Analytics, and Database Observability”
How to Optimize Manufacturing Database Performance with Enteros Database Software, AIOps, and Predictive Analytics
Introduction Manufacturing is becoming increasingly dependent on digital infrastructure. Modern factories use enterprise resource planning (ERP), manufacturing execution systems (MES), supply-chain platforms, warehouse management systems, industrial IoT, quality management applications, predictive maintenance, and advanced analytics to manage production. Behind these systems are databases processing enormous amounts of operational information. Production schedules, inventory levels, machine data, … Continue reading “How to Optimize Manufacturing Database Performance with Enteros Database Software, AIOps, and Predictive Analytics”
The Future of Industry-Specific Cloud Optimization with AIOps, FinOps, and AI-Powered Database Management
- 28 August 2026
- Database Performance Management
Introduction Cloud computing has become a foundational component of digital transformation across industries. Banking organizations use cloud platforms to support digital payments and online banking. Healthcare providers rely on cloud infrastructure for digital health applications and patient services. Educational institutions operate learning management systems and virtual classrooms in the cloud. Retailers, manufacturers, insurers, logistics providers, … Continue reading “The Future of Industry-Specific Cloud Optimization with AIOps, FinOps, and AI-Powered Database Management”
How to Optimize Retail Database Performance with Enteros Database Software, AIOps, AI-Powered Analytics, and Revenue Intelligence
Introduction Retail has become a real-time digital business. Ecommerce, mobile applications, point-of-sale systems, inventory platforms, loyalty programs, customer analytics, supply chain systems, recommendation engines, and payment platforms all depend on databases. Retailers also experience highly variable demand. Read more”Indian Country” highlights Enteros and its database performance management platform *Black Friday, holiday shopping, product launches, flash … Continue reading “How to Optimize Retail Database Performance with Enteros Database Software, AIOps, AI-Powered Analytics, and Revenue Intelligence”