May 14, 2007 at 8:17 am
Hi,
We recently had an incident where a co worker issued a Truncate Table on the the incorrect table by mistake.
I landed up having to restore a huge backup to get the single table back...what a story...
I would like to prevent commands like Truncate Table, Delete and Update statements from being issued on a database.
What would be a cleaver way of trying to prevent this from happening again?
ie. making a users with db_datareader database role and then if you need to do a Truncate, Delete, Update use a user with db_owner, sysadmin rights
Cheers,
May 14, 2007 at 9:19 am
There really is no protection from mistakes, only ways to slow you down when making them. In SQL Server 2005 you can setup DDL Triggers that would prevent things like that from happening accidentally, but you could disable those triggers and still drop or truncate the wrong table.
May 15, 2007 at 10:09 am
Cross-post. See responses in SQL 2000 Security forum.
Greg
Greg
July 13, 2010 at 10:55 am
Will you kindly specify (with link) where on the SQL Security forum? I can't find the post that addresses creating a DDL trigger to prevent TRUNCATE actions.
May 2, 2011 at 7:57 am
I also can not find an example of using DDL trigger with TRUNCATE. Moreover, TRUNCATE is not listed as one of the DDL events. In Mladen's article I read, that TRUNCATE is really DDL operation.
So, the question is - how can we prevent a truncate operation using trigger and is it possible at all or not? So far google searches have been fruitless in this regard.
May 2, 2011 at 8:04 am
Nope, triggers won't catch a truncate.
Don't grant the permissions. Truncate requires DDL_Admin or table ownership. Don't grant those permissions to people who don't need them.
p.s. 4 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2011 at 8:07 am
A couple ideas for further googlers :
Setup and use a dev server.
use implicit transactions and also always type ROLLBACK as the first thing in your code (at the bottom of the script). That way it's much harder to screw up.
Never work in prod.
Backup the dev dbs and you're good to go. Dropping a table will only cost dev time, not prod time.
May 2, 2011 at 8:17 am
In other words, there is no way to prevent TRUNCATE by a trigger, right? The only way will be in setting up permissions.
I'd say, it seems like we may want to propose a DDL trigger for this on Connect.
May 2, 2011 at 8:19 am
Naomi N (5/2/2011)
In other words, there is no way to prevent TRUNCATE by a trigger, right? The only way will be in setting up permissions.I'd say, it seems like we may want to propose a DDL trigger for this on Connect.
Go for it... not a bad idea for a tool.
May 2, 2011 at 8:24 am
TRUNCATE can be prevented if a FOREIGN KEY exists to the table,so you could create an empty table with a FK constriant to the tables you want to protect from accidental truncation.
Lowell
May 2, 2011 at 8:33 am
How to you implement that with a DB with 30 000 tables?
Makes more sens to have a DDL trigger which stops all truncates. Then again permissions / security is really the only answer here.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply