-- add user-defined error messages USE master GO sp_addmessage 50001,16,'Objects cannot be dropped from this database, please contact your DBA',@with_log=FALSE,@lang='us_english' GO sp_addmessage 50002,16,'Objects cannot be altered in this database, please contact your DBA',@with_log=FALSE,@lang='us_english' GO -- create test table USE userdb GO CREATE TABLE Table_1 (column1 int); GO -- create DDL trigger to block DROP/ALTER CREATE TRIGGER [block_ddl] ON DATABASE FOR ALTER_PROCEDURE, DROP_PROCEDURE, ALTER_TABLE, DROP_TABLE, ALTER_FUNCTION, DROP_FUNCTION, ALTER_INDEX, DROP_INDEX, ALTER_VIEW, DROP_VIEW, ALTER_TRIGGER, DROP_TRIGGER AS SET NOCOUNT ON IF EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') LIKE '%DROP%' BEGIN RAISERROR(50001, 16, 1); ROLLBACK; END IF EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') LIKE '%ALTER%' BEGIN RAISERROR(50002, 16, 1); ROLLBACK; END GO -- test DDL trigger DROP TABLE Table_1; GO ALTER TABLE Table_1 ADD column2 varchar(10); GO -- test disable and drop DISABLE TRIGGER [block_ddl] ON DATABASE GO DROP TABLE Table_1; GO -- clean up steps DROP TRIGGER [block_ddl] ON DATABASE GO USE master GO sp_dropmessage 50001 GO sp_dropmessage 50002 GO