Prevent Databases From Being Dropped

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