I put together a quick example of a DDL trigger to prevent databases from being dropped for a forum post today, and figured I’d put it on the blog in case I need it again.
While the best way to prevent users from dropping databases is to not grant the necessary access to drop a database, it can sometimes come in handy to have a catch-all to stop any databases from being dropped. This trigger is currently very simple – if the trigger is active, then it prevents any database on the instance from being dropped. You must disable or delete the trigger in order to drop a database. A more advanced version could check the username or the hostname of the user trying to perform the delete, or could check a table that contains the names of the databases that can be dropped (you would then have to insert the name of your database to this table, and then drop the database).
CREATE DATABASE TestDB GO USE Master GO CREATE TRIGGER Trig_Prevent_Drop_Database ON ALL SERVER FOR DROP_DATABASE AS RAISERROR('Dropping of databases has been disabled on this server.', 16,1); ROLLBACK; GO DROP DATABASE TestDB -- Shouldn't work GO -- Drop the trigger to allow deletions (you could also -- disable the trigger and then reenable it) DROP TRIGGER Trig_Prevent_Drop_Database ON ALL SERVER GO DROP DATABASE TestDB -- Should work GO