CODEWITHSUNDEEP
triggerssql-server-2012eventtrigger
PseudoToad
PseudoToad

Reputation: 1574

SQL Server Trigger To Prevent Database Drop

I would like to put a server level trigger in place to prevent dropping of any database that is not a database snapshot. At first blush the below appears as if it should work but it never does. I've tried reversing the logic and that didn't help. Does anyone have an idea what I am doing wrong?

DECLARE @DBName NVARCHAR(100),
        @eventData XML;

SET @eventData = EVENTDATA();           
SELECT  @DBName = @eventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');

RAISERROR('Attempting delete of %s.', 10, 1, @DBName);

IF @DBName IN (SELECT name
               FROM sys.databases 
               WHERE source_database_id IS NOT NULL)
    BEGIN
        RAISERROR('[%s] was successfully dropped.', 10, 1, @DBname) WITH LOG;
    END;
ELSE         
    BEGIN
        RAISERROR('[%s] cannot be deleted without first disabling the server trigger "tgr_prevent_db_drop".', 10, 1, @DBname) WITH LOG;
        ROLLBACK;
    END;

The RAISERROR at the top always confirms the correct database (e.g. the one being deleted) and when I run the SELECT from sys.databases manually it always returns the appropriate data. Unfortunately, no matter what I do, this always falls into the "..was successfully dropped" section for both true databases and their database snapshots.

Upvotes: 2

Views: 1847

Answers (2)

Wimpie Ratte
Wimpie Ratte

Reputation: 396

I tried getting this to work as well, but ran into too many permission obstacles due to sys.databases only returning values visible by the current user. (And i couldn't get a reliable enough generic solution with "Executing As" attempts.)

Eventually i just decided to use the database name as a filter. Example:

DECLARE @DBName NVARCHAR(100),
        @eventData XML;

SET @eventData = EVENTDATA();           
SELECT  @DBName = @eventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');

RAISERROR('Attempting delete of %s.', 10, 1, @DBName);

IF Right(@DBName, 9) <> '_SnapShot' --Checking via db name due to permissions affecting sys.databases
    BEGIN
        RAISERROR('[%s] was successfully dropped.', 10, 1, @DBname) WITH LOG;
    END;
ELSE         
    BEGIN
        RAISERROR('[%s] cannot be deleted without first disabling the server trigger "tgr_prevent_db_drop".', 10, 1, @DBname) WITH LOG;
        ROLLBACK;
    END;

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

this condition is always true..

IF @DBName IN (SELECT name
               FROM sys.databases 
               WHERE source_database_id IS NOT NULL)
    BEGIN
        RAISERROR('[%s] was successfully dropped.', 10, 1, @DBname) WITH LOG;
    END;

so ,your database will be dropped no matter what..Instead move below else clause to above block

RAISERROR('[%s] cannot be deleted without first disabling the server trigger "tgr_prevent_db_drop".', 10, 1, @DBname) WITH LOG;
        ROLLBACK;

Upvotes: 0

Related Questions