Prevent Table Drop

Since there is no direct permission in SQL Server to drop a table, here are two techniques to implement that.

The first one is based on creating a view on the table with option SCHEMABINDING. When the SCHEMABINDING option is used the table cannot be modified in a way that will affect the view definition, as well as it cannot be dropped unless the view is dropped first.

The second method is using the new DDL triggers in SQL Server 2005. Defining a trigger for DROP_TABLE with rollback in the body will not allow dropping tables.

CREATE TABLE dbo.Foo (

 keycol INT PRIMARY KEY,

 datacol CHAR(1));

 

GO

 

-- Using view with SCHEMABINDING

CREATE VIEW DoNotDropFoo WITH SCHEMABINDING

AS

SELECT keycol, datacol

FROM dbo.Foo;

 

GO

 

-- Attempt to drop table Foo

DROP TABLE dbo.Foo;

 

GO

 

Msg 3729, Level 16, State 1, Line 3

Cannot DROP TABLE 'dbo.Foo' because it is being referenced by object 'DoNotDropFoo'.

 

 

-- Using DDL trigger

CREATE TRIGGER DoNotDropTables

ON DATABASE

FOR DROP_TABLE

AS

  RAISERROR ('Cannot drop tables!', 10, 1);

  ROLLBACK;

 

GO

 

-- Attempt to drop table Foo

DROP TABLE dbo.Foo;

 

GO

 

Cannot drop tables!

Msg 3609, Level 16, State 2, Line 3

The transaction ended in the trigger. The batch has been aborted.

2 replies
  1. Mehrdad
    Mehrdad says:

    I used the trigger and has ended up with this annoying error message:
    The specified event type(s) is/are not valid on the specified target object.
    I can’t figure what the heck is this but its related to the “For Drop_Table” line! Any thoughts?

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *