Block Database creation on Server by DDL Trigger

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

Secure Your Data: Simple solution to Encrypt and Decrypt values in SQL Server 2005

Saving the sensitive data in database always have the concerns of data privacy. I have seen some database designs where database architects saves clear text passwords in tables which is very very dangerous.
People have different reasons to justify this approach but in my opinion, whatever you do…there is always a chance to access your database table for a dedicated hacker. Once he or she is able to read the password column then the whole security collapse……So the simple rules for security is:

– Never ever save clear text passwords
– Divide the encryption mechanism in different layers accessible by different roles so even if hacker is able to break a database layer, he or she will still have half information to decode so you are still safe.
– Never trust a single person for all of your security. Always try to assigns roles, responsible for security, to different people.

As a simple solution for above mentioned issue, here is a basic SQL Server 2005 implementation. A person having sysadmin role even can not decode these values without having the actual passphrase or encryption key which will be provided by Business Layer through another encrypted configuration file.

–Create a temporary table to hold encrypted data for our example
Declare @m_testTable Table
(
encryptedText varbinary(8000)
)

—Encrypt sample data and insert it into above defined table
Insert into @m_testTable (encryptedText)
Select EncryptbyPassPhrase(‘myPassword’,‘Text to be encrypted’)

—Display the encrypted value
Select encryptedText as EncryptedValue from @m_testTable

–Decrypt the above encrypted value and display it
Select Cast(DecryptbyPassPhrase(‘myPassword’,encryptedText) as varchar(8000)) OriginalText from @m_testTable