July 14, 2011 at 9:29 am
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?
July 14, 2011 at 9:44 am
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
July 14, 2011 at 10:21 am
Thanks for the quick response. This is exactly what I needed and it works perfectly. 🙂
July 14, 2011 at 10:36 am
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
July 14, 2011 at 10:38 am
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
July 14, 2011 at 12:43 pm
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!
July 14, 2011 at 12:50 pm
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
July 14, 2011 at 1:40 pm
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?
July 14, 2011 at 1:42 pm
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
July 14, 2011 at 2:01 pm
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.
July 18, 2011 at 6:36 am
If they have sysadmin rights, they can disable the triggers and then drop the table anyway. That negates the whole point of what you're trying to do.
- 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
July 19, 2011 at 2:17 pm
upstart (7/14/2011)
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!
Consider moving your audit tables to a separate database, remove the developers from SYSADMIN, and then add them as members of DBO on the existing database. The problem with SYSADMIN is that it grants them permission to do all sorts of things beyond just creating and dropping objects in a specific database.
Start out by making a copy of the audit tables into the new audit database, create a new test user with same membership as developers currently have, and experiment with the test user until it's all working as needed. Only then alter the existing tables and users.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 23, 2013 at 9:53 am
Slightly confused here... I tried this on my TEST server and I cannot drop any tables.
When I try to drop the [cherries] table, I get the following message:-
DROP TABLE Issued.
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure safety, Line 8
Tables cannot be dropped in this database.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
April 23, 2013 at 10:04 am
Andy did you copy the trigger verbatim or modify it a bit?
it worked fine for me when i posted to the thread originally;
can we see your version of the trigger, just to be sure?
Lowell
April 23, 2013 at 10:12 am
Crap. Sorry am being stupid. Ignore my previous post.
Anyways, I am hoping to use your script as a starting point to prevent circa 400+ tables from being dropped. I have tried a few things but I am not how to pass the table values to @oname?
Any ideas appreciated. Thanks.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply