How to Prevent DROP Table using SQL Server DDL Trigger
T-SQL developers and SQL Server administrators can prevent unauthorized users to drop sql tables using sql DROP TABLE command on their databases with the use of SQL Server DDL trigger.
Using DDL Trigger is respectively a new method of preventing database tables compared to using Create View With SchemaBinding in SQL Server.
DDL Trigger are new with Microsoft SQL Server 2005.
Since then the use of DDL trigger in SQL Server 2005 or ddl trigger in SQL Server 2008 is increasing parallel with the info distributed among SQL Server professionals.
Here is the basic drop table DDL trigger in SQL Server.
This will help SQL professionals to secure their database tables against sql injections, etc.
CREATE Trigger ddlt_PreventDDLDropTable
ON DATABASE FOR DROP_TABLE
AS
RAISERROR ('Unauthorized DROP TABLE', 10, 1);
ROLLBACK;
GO
And t-sql developers can find a more ehnanced version of the above DDL trigger which also returns meta data about the event causing the trigger to fire.
SQL security administrators can use the information from EVENTDATA for audit and security purposes.
CREATE Trigger ddlt_PreventDDLDropTable
ON DATABASE FOR DROP_TABLE
AS
SELECT
EventType = EVENTDATA().value('(EVENT_INSTANCE/EventType)[1]', 'sysname'),
PostTime = EVENTDATA().value('(EVENT_INSTANCE/PostTime)[1]', 'datetime'),
LoginName = EVENTDATA().value('(EVENT_INSTANCE/LoginName)[1]', 'sysname'),
UserName = EVENTDATA().value('(EVENT_INSTANCE/UserName)[1]', 'sysname'),
DatabaseName = EVENTDATA().value('(EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),
SchemaName = EVENTDATA().value('(EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
ObjectName = EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
ObjectType = EVENTDATA().value('(EVENT_INSTANCE/ObjectType)[1]', 'sysname'),
CommandText = EVENTDATA().value('(EVENT_INSTANCE//TSQLCommand[1]/CommandText)[1]', 'nvarchar(max)')
RAISERROR ('You can not drop table in this database', 10, 1);
ROLLBACK;
GO
Let's create with a sql example.
First create a sql database table.
Second create the drop table DDL trigger.
Third, execute the following sql command to test how t-sql ddl trigger behave.
DROP TABLE DDLDropTable
After sql DROP Table command is executed, the message displayed from the DDL trigger execution is as follows :
You can not drop table in this database
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
If you want to take a closer look at the EVENTDATA(), here is the output of the above DDL trigger event data.
<EVENT_INSTANCE>
<EventType>DROP_TABLE</EventType>
<PostTime>2011-01-06T09:12:24.013</PostTime>
<SPID>51</SPID>
<ServerName>ISTW4890</ServerName>
<LoginName>TR\yilmazer</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks2008R2</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>DDLDropTable</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>DROP TABLE DDLDropTable</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>