Post reply

Deny DDL except enabling/disabling constraints

  • September 5, 2008 at 3:52 am

    #190108

    Hi,

    i want to deny structural changes on a database. DML must be allowed, DDL not. I do this, like follows:

    CREATE TRIGGER trig_deny_ddl

    ON database

    for DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS

    AS

    print 'Strukturelle Änderungen auf dieser DB verboten. Bitte wenden Sie sich an den Administrator'

    rollback;

    go

    For performance reasons some code in the db disables constraints does some inserts, deletes and enables the constraints later. As this is DDL (Alter table..) I have to somehow extend the trigger so that it allows enabling/disabling the constraints.

    Can you help me?

    Thanks,

    Tobias

  • September 5, 2008 at 5:54 am

    #867681

    Ok, i found out the solution:

    alter TRIGGER [trig_deny_ddl]

    ON database

    for DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS

    AS

    declare @command nvarchar(max)

    SELECT @command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    if @command not like 'alter table%check constraint %'

    begin

    print 'Strukturelle Änderungen auf dieser DB verboten. Bitte wenden Sie sich an den Administrator'

    rollback;

    end

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

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