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

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.

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

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.

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

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.

Rajendra Gupta
Azure, DDL, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

603 Views