DDL trigger to prevent drop of a particular database

  • Can I write a DDL trigger to prevent drop of a particular database? I tried this but didn't work:

    CREATE TRIGGER Prevent_DB_Drop

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    DECLARE @oname NVARCHAR(100)

    SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    IF @oname IN ('testDB')

    BEGIN

    PRINT 'You are not allowed to drop this Database.'

    ROLLBACK;

    END

    GO

    ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER

    drop database test

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • you had most of it, it looks like, but you forgot to handle the EVENTDATA() function, which ahs the currently scoped event information

    CREATE TRIGGER Prevent_DB_Drop

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    DECLARE @oname NVARCHAR(100),

    @eventData XML

    --set the EVENTDATA() functions value to our xml so we can parse it

    SET @eventData = EVENTDATA()

    SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    IF @oname IN ('testDB')

    BEGIN

    PRINT 'You are not allowed to drop this Database.'

    ROLLBACK;

    END

    GO

    ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Interestingly I got this script from one of your script for tables and modified it for database. While pasting here, I missed the part that you added. But after all this , it doesn't work.

    I can happily drop the database with sa account without any error or warning.

    Lowell (6/27/2013)


    you had most of it, it looks like, but you forgot to handle the EVENTDATA() function, which ahs the currently scoped event information

    CREATE TRIGGER Prevent_DB_Drop

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    DECLARE @oname NVARCHAR(100),

    @eventData XML

    --set the EVENTDATA() functions value to our xml so we can parse it

    SET @eventData = EVENTDATA()

    SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    IF @oname IN ('testDB')

    BEGIN

    PRINT 'You are not allowed to drop this Database.'

    ROLLBACK;

    END

    GO

    ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • ahh, i see it now;

    objectname is fine for objects, like tables/procs(anything that exists in sys.objects), but you need a different field in the xml data for the database name

    CREATE TRIGGER Prevent_DB_Drop

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    DECLARE @DatabaseName NVARCHAR(100),

    @eventData XML

    --set the EVENTDATA() functions value to our xml so we can parse it

    SET @eventData = EVENTDATA()

    SELECT @DatabaseName = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),

    IF @DatabaseName IN ('testDB')

    BEGIN

    PRINT 'You are not allowed to drop this Database.'

    ROLLBACK;

    END

    GO

    ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Great Sir. it worked. thanks

    Lowell (6/27/2013)


    ahh, i see it now;

    objectname is fine for objects, like tables/procs(anything that exists in sys.objects), but you need a different field in the xml data for the database name

    CREATE TRIGGER Prevent_DB_Drop

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    DECLARE @DatabaseName NVARCHAR(100),

    @eventData XML

    --set the EVENTDATA() functions value to our xml so we can parse it

    SET @eventData = EVENTDATA()

    SELECT @DatabaseName = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),

    IF @DatabaseName IN ('testDB')

    BEGIN

    PRINT 'You are not allowed to drop this Database.'

    ROLLBACK;

    END

    GO

    ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply