Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DDL Trigger to prevent dropping of a specific table?


DDL Trigger to prevent dropping of a specific table?

Author
Message
upstart
upstart
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 327
Hi,

I know DDL Triggers can monitor changes on both the Server level and Database level, which could track/prevent changes for all Tables in a dB, but is there a way to craft a trigger such that it only prevents the dropping of 1, specific table?

And if not, is there another way to prevent a table from getting dropped?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14986 Visits: 39017
yes.
i just created and tested this trigger as a prototype.
i've created 4 tables, and tried to delete them, only one is successful, the others raise the expected error.

CREATE TRIGGER [TR_ProtectCriticalTables]
ON DATABASE
FOR
DROP_TABLE

AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @oname IN ('tblBananas','tblApples','tblOranges')
BEGIN
DECLARE @err varchar(100)
SET @err = 'Table ' + @oname + ' is super duper protected and cannot be dropped.'
RAISERROR (@err, 16, 1) ;
ROLLBACK;
END
GO
ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE




CREATE TABLE [dbo].[tblBananas] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [dbo].[tblCherries] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [dbo].[tblApples] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [dbo].[tblOranges] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)

DROP TABLE tblBananas
DROP TABLE dbo.[tblCherries]
DROP TABLE [dbo].[tblApples]
DROP TABLE [tblOranges]



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

upstart
upstart
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 327
Thanks for the quick response. This is exactly what I needed and it works perfectly. :-)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14986 Visits: 39017
don't know if you use other schemas than the default dbo, but this slight modification to the model prevents dbl.tblBananas from being dropped, but not TestSchema.tblBananas; probably a good enhancement just in case for the future:

ALTER TRIGGER [TR_ProtectCriticalTables]
ON DATABASE
FOR
DROP_TABLE

AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@sname NVARCHAR(100),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@sname=@eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @oname IN ('tblBananas','tblApples','tblOranges') and @sname = 'dbo'
BEGIN
DECLARE @err varchar(100)
SET @err = 'Table ' + @sname + '.' + @oname + ' is super duper protected and cannot be dropped.'
RAISERROR (@err, 16, 1) ;
ROLLBACK;
END
GO
ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE






CREATE SCHEMA TestSchema
CREATE TABLE [TestSchema].[tblBananas] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)

DROP TABLE [TestSchema].[tblBananas]



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14387 Visits: 9729
I have to ask: Why are you worried about a table being dropped? Why is it okay if other tables get dropped?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
upstart
upstart
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 327
Well, the reason I am worrying about this one specific table being dropped is because it is used to store information from another DDL trigger we have setup on the database that monitors all database actions.

Last week, one of our developers dropped every table/proc/view from the dbo. schema on our development box and either covered their tracks really, really well or our default 'black box' trace for some reason did not capture anything regarding it. We only lost a day of coding since we had a full backup from 1am to restore from. Since then, we implemented 1 DDL trigger to put all database events into a table and want to prevent the developers from dropping it.

So, in essence, if someone decides to drop everything again, at least we will still have this table to tell us what happened. Hmmmm.....unless they delete everything from the table? I may have to think about how to handle that issue as well.

Edit: decided on putting a normal table trigger on this table to prevent deletions. Also, modified the original DDL trigger to not log anything to do with Updating Statistics since it creates about 400 records a night that really serve no purpose and added a weekly job to remove records older then 7 days.

....Auditing...fun for the whole family!
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14986 Visits: 39017
upstart (7/14/2011)
... either covered their tracks really, really well or our default 'black box' trace for some reason did not capture anything regarding it....


In my career I've seen mostly careless mistakes and bad assumptions that affect schemas/ data like that.
I haven't found an evil developer covering his tracks yet...but i've seen a few posts here on SSC for newbies wanting to hide their big errors so they don't get fired.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

upstart
upstart
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 327
OK, so know I have a DDL trigger that logs all events, a DDL trigger to prevent this specific table from being dropped, and a normal table trigger to prevent the deletion or updates of records from this table.

I can't think of any other action a user can do to the table other then Truncating it. Is there a way to prevent Truncating a table? Won't truncating override any triggers on the table?
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14387 Visits: 9729
How about simply denying them permission to access that database through normal SQL Server security practices. The DDL trigger to log database events will need to have permission to write to the table, but it can run under an account designed for that. Then you eliminate whole levels of complexity.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
upstart
upstart
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 327
I thought of this as well, but I think because this is a Development box, we have a lot of people setup as sysadmins who need access to drop/create/alter objects at will.

Because of the natural sysadmin rights, wouldn't they basically be able to bypass all of the extra security or just undo it if they needed?

Maybe security is the answer and I am doing overkill with the Triggers and Logging...but I had some free-time to kill and it taught me a little something about DDL trigger I guess.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search