Prevent Truncate, Delete and Update Statements
-
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,
-
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.
-
Cross-post. See responses in SQL 2000 Security forum.
Greg
Greg
-
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.
-
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
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.
-
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.
-
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
--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! -
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