How to make a table undroppable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make a table undroppable

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-14 : 17:18:19

In our system we drop and add tables constantly

however there are some tables that must never ever be dropped.

Is there a way to add some type of constraint to make a table impossible to drop?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:24:05

quote:
Permissions to drop a table

Requires CONTROL permission on the table, or membership in the db_ddladmin fixed database role, is required.

E 12°55'05.25"
N 56°04'39.16"

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-14 : 17:30:20

quote:
Originally posted by Peso

quote:
Permissions to drop a table

Requires CONTROL permission on the table, or membership in the db_ddladmin fixed database role, is required.

E 12°55'05.25"
N 56°04'39.16"

i went into table properties, then hit permissions, added my name, and hit deny on "CONTROL". Does anything else under Deny need to be checked off?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:32:36

Or implement a DDL trigger like this

CREATE TABLE Peso (Yak INT)
GO

CREATE TRIGGER NoPesoDrop
ON DATABASE 
FOR DROP_TABLE
AS 
   RAISERROR('You must disable Trigger "NoPesoDrop" to drop table.', 16, 1)
   ROLLBACK
GO

DROP TABLE Peso
GO

E 12°55'05.25"
N 56°04'39.16"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-14 : 17:34:09

Peso, you are right.
But i think there is another way using this new kind of trigger - DDL-Trigger.
Or is this not a recommended way?

Greetings
Webfred

Too Old to Rock 'n' Roll, Too Young to Die

Edit: ~~Triger~~ to Trigger

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-14 : 17:34:58

Argh - too late again

Too Old to Rock 'n' Roll, Too Young to Die

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 17:43:17

[code]CREATE TABLE Peso (Yak INT)
GO

ALTER TRIGGER NoPesoDrop
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
DECLARE @s VARCHAR(MAX)
SET @s = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')

RAISERROR('You must disable Trigger "NoPesoDrop" to execute "%s".', 16, 1, @s)
ROLLBACK
GO

DROP TABLE Peso
GO

ALTER TABLE Peso
ALTER COLUMN Yak SMALLINT
GO[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-14 : 18:10:51

"make a table impossible to drop"

If the person trying to do it is a sysadmin or in the db_owner role, there isn't much you can do to stop them. The DDL trigger might slow them down a bit until they run drop or disable trigger NoPesoDrop.

Also, that trigger doesn't really do the job, since it would prevent dropping all tables, and the OPs requirement was to only prevent dropping certain tables.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 18:13:26

You can examine the data from EVENTDATA to see which table is used for DROP TABLE statement?

E 12°55'05.25"
N 56°04'39.16"

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-15 : 00:44:27

quote:
Originally posted by Michael Valentine Jones

"make a table impossible to drop"

If the person trying to do it is a sysadmin or in the db_owner role, there isn't much you can do to stop them. The DDL trigger might slow them down a bit until they run drop or disable trigger NoPesoDrop.

Also, that trigger doesn't really do the job, since it would prevent dropping all tables, and the OPs requirement was to only prevent dropping certain tables.

CODO ERGO SUM

Not impossible to drop, it just so happened today that i almost dropped a very important table, i just want to make it so that you cant just accidently type

deletE TABLE employees

etc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 02:35:16

How can you "accidentally" drop a table?

1) You have to type the code syntaxial correct
2) You have to execute the code

At least two steps to do manually before the table is dropped...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources