Jun 172008
 

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
 

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>