Deny DDL except enabling/disabling constraints

• Tobias Kiemes

  SSCommitted

  Points: 1604

  More actions

  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

• Tobias Kiemes

  SSCommitted

  Points: 1604

  More actions

  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