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’)
‘Database cannot be dropped from the Server. Please contact Administrator’
Please share if any other alternative is there as well.