SQL Monitoring with DDL Triggers

Leave a comment (2) Go to comments

We can create a DDL trigger to audit operations such as creating or dropping tables. DDL triggers are a very powerful tool for monitoring the actions that occur in a database.

The other category of trigger is standard triggers, which can only respond to changes in data, data definition language (DDL) triggers can be used to respond to changes to objects in a database.

DDL triggers fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

DDL triggers can be used for administrative tasks such as auditing and regulating database operations. DDL triggers can fire in response to a Transact-SQL event that is processed in the current database or on the current server. The scope of the trigger depends on the event.

DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server. The scope of the trigger depends on the event. For example, a DDL trigger created to fire in response to a CREATE_TABLE event can do so whenever a CREATE_TABLE event occurs in the database, or on the server instance. A DDL trigger created to fire in response to a CREATE_LOGIN event can do so only when a CREATE_LOGIN event occurs in the server.


Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL triggers appear in the Database Triggers folder. This folder is located under the Programmability folder of the corresponding database.

To create a trigger, use the CREATE TRIGGER T-SQL command. DDL triggers, like standard triggers, execute stored procedures in response to an event. But unlike standard triggers, they do not execute in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they primarily execute in response to data definition language (DDL) statements. These include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

Creating a DDL trigger is just as simple as creating a DML trigger. Here is an example of a trigger that would fire whenever there is a DROP_TABLE or ALTER_TABLE event.

Example 1 : Audit Alter Table  and Drop Table Statement

CREATE TRIGGER tr_prevent_tableChange

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

BEGIN
print 'your statement is logged for auditing purpose'

ROLLBACK
END
 

Example 2 : Make sure that no one can create database on your server except the DBA.

Use master

Go

Create Trigger tr_Prevent_DatabaseCreation

ON ALL Server

FOR CREATE_DATABASE

As

Begin

Print 'Only DBA can create new databases on this server.'

Rollback;

End

Go

Now, lets try the above trigger:

CREATE DATABASE [Test_DB]

You will get the following result.

Only DBA can create new databases on this server.

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

Incase, DBA wanted to create a database then, DBA can disable this trigger as follows and can create database as usual

DISABLE TRIGGER trStopDatabaseCreation ON ALL SERVER

EOF - SQL Monitoring with DDL Triggers, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

2 Comments.

  1. Thanks! Very nice examples.
    On example 2. Is there a way to know the name of the current database being created? I wanted to add to the trigger something like
    USE @newdbname
    GO
    EXEC sp_addrolemember N’db_owner’, ‘some_account_name’

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.