Usage of DDL TRIGGER to restrict users on DROP procedure

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 411
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.*/

Comments or Responses

Login to post response