SQL Server Prevent Accidental Database Drop with DDL Triggers
Today I am going to share a very simple yet useful piece of code that I have implemented in many SQL instances over the years to prevent the databases from getting dropped accidentally. One of the simplest way is to take the help of DDL triggers that was introduced since SQL Server 2005. Below is the code that I have implemented:
CREATE TRIGGER [Trig_Prevent_DB_Drop] ON ALL SERVER
FOR DROP_DATABASE AS
FOR DROP_DATABASE AS
IF SUSER_NAME() NOT IN (‘sa’)
BEGIN
RAISERROR (
‘Database cannot be dropped from the Server. Please contact Administrator’
,16
,1
);
BEGIN
RAISERROR (
‘Database cannot be dropped from the Server. Please contact Administrator’
,16
,1
);
ROLLBACK;
END
GO