Sample DDL trigger to restrict other users , not to allow to drop any procedure in database
CREATE TRIGGER RestrictDropStoredProcedure
ON DATABASE
FOR DROP_PROCEDURE
AS
PRINT 'Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script.
Script : DISABLE TRIGGER PreventDropSP ON DATABASE; <Run your DROP SP>; ENABLE TRIGGER PreventDropSP ON DATABASE;'
ROLLBACK;
GO
--Test DDL Trigger by executing DROP PROCEDURE command
DROP PROCEDURE TestSP;
GO
Output from above test is as follows:
/*Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script.
Script : DISABLE TRIGGER PreventDropSP ON DATABASE; <Run your DROP SP>; ENABLE TRIGGER PreventDropSP ON DATABASE;
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.*/