Tags

, , , ,


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

IF SUSER_NAME() NOT IN (‘sa’)
BEGIN
RAISERROR (
‘Database cannot be dropped from the Server. Please contact Administrator’
,16
,1
);

ROLLBACK;

END

GO

Please share if any other alternative is there as well.

Cheers,

Subhro Saha

Advertisements