SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.

sql-drop-table-ddl-trigger-in-sql-server-2008-r2

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>





Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems