TSQL Tip O’ the Week – 8/9/11 – DDL Triggers for Extra Protection

This weeks tip:

Add extra protection to your databases by adding a DDL trigger that will prevent users from deleting your databases.

Why?

In the last year, five times a user deleted the wrong database by mistake in SQL Server Management Studio – each time they thought they were connected to their local machine, but they were really connect to a shared development environment.  If we would have had these triggers on our shared environments, we wouldn’t have wasted anytime restoring the databases.

Here’s a great article from Chriss K on MSDN with code showing how easy it is to add this extra layer of protection.

- Code Example to first LOG then prevent SQL Database from being deleted

Here’s the video from the post, but the code is in the article – note it is really easy:

Note – If you need to delete the database, you can simply disable the trigger with one line of code – the disable trigger statement.

Here are some related posts from the intraweb:

• From Technet – DDL Triggers
• From MSDN – Designing DDL Triggers
• From MSDN  – CREATE TRIGGER (Transact-SQL)

Previous Tips O’ the Week

• TSQL Tip O’ the Week – 8/2/11 – Records Per Day
• TSQL Tip O’ the Week – 7/26/11 – A Column to CSV
• TSQL Tip O’ the Week – 7/19/11 – GetUTCDate
• The Entire List…