SQL Tips by Namwar Rizvi

June 19, 2007

Block Database creation on Server by DDL Trigger

Filed under: DDL,Information,New Features,Query,Security,SQL Server 2005,tips,TSQL — namwar @ 8:40 PM

DDL triggers are one of the best features of SQL Server 2005. It provides you great power and flexibility in implementing different scenarios which were either impossible or very difficult before. DDL Triggers are of two different types:

  1. DDL Triggers for Database
  2. DDL Triggers for Server

For example, you want to make sure that no one can create database on your server except the DBA. You can now easily achieve this by implementing the following trigger:

Use master
Go

Create Trigger trStopDatabaseCreation
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 [TestDB] ON PRIMARY
( NAME = N‘TestDB’, FILENAME = N‘C:\SQL_Data\TestDB.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON

( NAME = N‘TestDB_log’, FILENAME = N‘C:\SQL_Log\TestDB_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO

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.

The DBA can disable this trigger as follows and can create database as usual

DISABLE TRIGGER trStopDatabaseCreation ON ALL SERVER

For further information please refer to SQL Server Books Online

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: