If developers have their own login to SQL Server and it does not have permissions to drop or create objects this will not be issue at all. However, there are still risk of System Admin himself making accidental mistakes. The solution to this will be use Server and Database Level DDL Triggers.
DDL is abbreviation of Data Definition Level. DDL contains schema of the database object. It was always dream of all DBA, when change in mission critical schema of the database or server is attempted it is prevented immediately informing DBA and users automatically. DDL Trigger can now make this dream true. Definition of DDL Trigger (from BOL) is DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.
Following example demonstrates how DDL trigger can be used to prevent dropping stored procedure.
I have a sample stored procedure Created
Step 2 :
Create DDL trigger which will prevent dropping the stored procedure.
CREATE TRIGGER PreventDropSP
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>;’
Step 3 :
Now test above trigger by attempting to drop the stored procedure.
DROP PROCEDURE dbo.usp_SelectRecordsByPlayerName
This should throw following message along with error code 3609 :
Dropping Procedure is not allowed.
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>;
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Step 4 :
Now DISABLE above trigger and it will let you successfully drop the stored procedure previously attempted to drop. Once it is dropped enable trigger again to prevent future accidents.
DISABLE TRIGGER PreventDropSP ON DATABASE;
DROP PROCEDURE dbo.usp_SelectRecordsByPlayerName;
ENABLE TRIGGER PreventDropSP ON DATABASE;