Server Scoped DDL Trigger in SQL Server

Server-Scoped DDL Trigger in SQL Server

In this article, I will discuss the Server-Scoped DDL Trigger in SQL Server with some examples. Please read our last article before proceeding to this article where we discussed the Database Scoped DDL Trigger in SQL Server.

Let’s understand the need for a Server-Scoped DDL Trigger with an example.

If you remember we created the following trigger in our last article.
CREATE TRIGGER trRestrictDDLEvents
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN 
   PRINT 'You cannot create, alter or drop a table'
   ROLLBACK TRANSACTION
END

The above trigger is an example of Database Scoped DDL Trigger. The above Trigger will prevent the users from creating, altering or dropping tables only from the database on which it is created. 

But, if we have another database on the same server, then the users will be able to create, alter or drop tables in that database. So, if we want to prevent the users from creating, altering or dropping tables from that database then we need to create the trigger again in that particular database.

Think a situation, where we have 50 databases on a particular server and we want to prevent the users from creating, altering or dropping tables from all those 50 databases. Creating the same trigger again and again for all those 50 databases is bad for the following two reasons

1. It is a tedious process as well as error prone

2. Maintainability is a nightmare. If for some reason we have to change the trigger, then we will have to do it in all the 50 databases, which again is a tedious process as well as error prone.

This is the ideal situation where the Server-Scoped DDL triggers come into the picture. When we create a server-scoped DDL trigger, then it will be fired in response to the DDL events happening in all of the databases on that particular server.

Creating a Server-Scoped DDL Trigger in SQL Server is very much similar to creating a database scoped DDL trigger, except that we will have to change the scope to ALL Server as shown in the below example.

CREATE TRIGGER trServerScopedDDLTrigger
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN 
   PRINT 'You cannot create, alter or drop a table in any database of this server'
   ROLLBACK TRANSACTION
END

Now if you will try to create, alter or drop a table in any of the databases on that particular server, then the above Server-Scoped DDL trigger will be fired.

Where can I find the Server-scoped DDL triggers?

To find the Server-Scoped DDL Triggers in SQL Server Follow the below steps

1. In the Object Explorer window, expand the “Server Objects” folder

2. Then Expand Triggers folder as shown in the below image

Server-Scoped DDL Trigger in SQL Server

To disable the Server-Scoped DDL trigger in SQL Server

1. Right click on the trigger in object explorer and select “Disable” from the context menu

2. We can also disable the trigger using the SQL Command as

DISABLE TRIGGER trServerScopedDDLTrigger ON ALL SERVER

To enable the Server-Scoped DDL trigger

1. Right click on the trigger in object explorer and select “Enable” from the context menu

2. We can also enable the trigger using the T-SQL command as

ENABLE TRIGGER trServerScopedDDLTrigger ON ALL SERVER 

To drop the Server-scoped DDL trigger

1. Right click on the trigger in object explorer and select “Delete” from the context menu

2. We can also drop the trigger using the SQL command as

DROP TRIGGER trServerScopedDDLTrigger ON ALL SERVER

In the next article, I will discuss the Triggers Execution Order in SQL Server with some examples.

SUMMARY

In this article, I try to explain the Server-Scoped DDL Trigger in SQL Server step by step with a real-time example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

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