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.