During a recent meeting with an offshore team from a large database consultancy company, we discussed various security issues, one of which was accidental data modification by developers and DBAs themselves. In this article, we will explore the concept of Server and Database Level DDL Triggers and how they can help prevent such accidents.
Accidental execution of code or fat fingers can lead to unintended modifications in the database. One way to mitigate this risk is by assigning the correct permissions to developers and DBAs. If they do not have permissions to drop or create objects, the risk of accidental modification is significantly reduced. However, there is still a possibility of system admins making accidental mistakes.
This is where Server and Database Level DDL Triggers come into play. DDL stands for Data Definition Language, which contains the schema of the database object. DDL Triggers are a special kind of trigger that fire in response to DDL statements. They can be used to perform administrative tasks in the database, such as auditing and regulating database operations.
Unlike DML (Data Manipulation Language) triggers, DDL triggers do not support the INSTEAD OF feature and do not have the feature of inserted and deleted tables. DDL triggers are not involved in database manipulations.
Let’s take a look at an example of how a DDL trigger can be used to prevent the dropping of a stored procedure:
USE AdventureWorks;
CREATE PROCEDURE TestSP
AS
SELECT 1 test;
CREATE TRIGGER PreventDropSP
ON DATABASE
FOR DROP_PROCEDURE
AS
PRINT 'Dropping Procedure is not allowed. DDL Trigger is preventing this from happening.
To drop the stored procedure, run the following script:
Script: DISABLE TRIGGER PreventDropSP ON DATABASE;
<Run your DROP SP>
ENABLE TRIGGER PreventDropSP ON DATABASE;'
ROLLBACK;
USE AdventureWorks;
DROP PROCEDURE TestSP;When attempting to drop the stored procedure, the DDL trigger will prevent it and display the following message along with error code 3609:
Dropping Procedure is not allowed. DDL Trigger is preventing this from happening.
To drop the stored procedure, run the following script:
Script: DISABLE TRIGGER PreventDropSP ON DATABASE;
<Run your DROP SP>;
ENABLE TRIGGER PreventDropSP ON DATABASE;To successfully drop the stored procedure, you can disable the trigger using the following script:
USE AdventureWorks;
DISABLE TRIGGER PreventDropSP ON DATABASE;
DROP PROCEDURE TestSP;
ENABLE TRIGGER PreventDropSP ON DATABASE;By enabling and disabling the trigger as needed, you can prevent future accidents while still allowing intentional modifications.
It is important to note that there are various DDL events (such as DROP_PROCEDURE in the example above) that can be used with DDL triggers. A list of all these events can be found on the MSDN website.
By implementing Server and Database Level DDL Triggers, you can add an extra layer of security to your SQL Server environment and prevent accidental data modifications. This can help ensure the integrity and stability of your database.
Reference: Pinal Dave (http://www.SQLAuthority.com), BOL