SQL SERVER – Trigger on Database to Prevent Table Creation

In this blog post, we will be discussing a very interesting scenario of Trigger on Database to Prevent Table Creation. I recently discussed this with my client Comprehensive Database Performance Health Check the solution to it when we were fixing their Server’s performance.

I personally do not like triggers and I also do not like the solution which I am going to propose in the blog post. If it was my database, I would have created a user without permission to create a table. However, I had limited choice and had to create a database trigger for my client.

Trigger on Database

The requirement was to not allow any user to create any table in the specific database. Here is how you can create a trigger that will prevent the creation of the new database.

CREATE TRIGGER StopTableCreation ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'This database does not allow new tables.'
ROLLBACK TRANSACTION
GO

Once you create the above trigger, run the following code which will create a new table.

CREATE TABLE T1 (ID INT)
GO

When you run the script above, it will give you the following error.

This database does not allow new tables.
Msg 3609, Level 16, State 2, Line 8
The transaction ended in the trigger. The batch has been aborted.

This is because now no new tables will be allowed to be created in the database. If you have any questions, please feel free to reach out to me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)