Prevent Database Deletion from SSMS GUI – How to

sql serversql server 2014ssms

Using SQL Server Management Studio, it is possible to right-click a database in the Object Explorer and delete it. (You can also delete a database using T-SQL, not demonstrated here.) I accidentally deleted the wrong database (thankfully, I was able to restore from a recent backup).

Still, it got me thinking. Is there a way to configure a database against accidental deletion — by GUI or by T-SQL Script? If I actually need to remove a specific database, then I should have to take a few deliberate steps to enable database deletion. Otherwise the database cannot be deleted.

I searched for "prevent database deletion" and all I found were how-to articles about how to prevent table row data from being deleted — not accidental deletion of the entire database.

SSMS Object Explorer, about to delete a database

Best Answer

The suggestion given by Phil prevents all databases in the server from being deleted. What if there's only the need to protect one database on the server from accidental deletion?

Let's imagine this database is named ProductionDatabase. I managed to achieve that with the following code:

CREATE OR ALTER TRIGGER TRG_PreventDropProductionDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    IF DB_NAME() = 'ProductionDatabase'
        BEGIN
            RAISERROR('You cannot drop the production database.', 16,1);
            ROLLBACK;
        END
END
GO
Related Question