Server DDL trigger not working

• Michas Konstantinos-132761

  Mr or Mrs. 500

  Points: 552

  More actions

  April 26, 2007 at 2:46 am

  #173106

  Hello everyone,

  I have a DDL trigger that theoritically protects my Server from altering my

  databases. Here it is:

  /****** Object: DdlTrigger [STOP_DDL_On_Databases] Script Date: 04/24/2007

  14:05:03 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TRIGGER [STOP_DDL_On_Databases]

  ON ALL SERVER

  FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE

  AS

  PRINT 'HELLO, You are not allowed to CREATE, ALTER and DROP any Databases.'

  ROLLBACK;

  GO

  SET ANSI_NULLS OFF

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  ENABLE TRIGGER [STOP_DDL_On_Databases] ON ALL SERVER

  GO

  Now when I execute an alter database statement, in a query window, to set

  the user level to Single:

  Alter Database SEAA_STOLOS set SINGLE_USER

  The DDL trigger runs and prints the message "HELLO,..." but it does not

  rollback the ALTER DATABASE statement.

  I have the Developer Edition and recently applied the KB934458 hot fix. That

  fix failed in database engine the first times that tried to apply it and I

  assume that the reason is the worm protector of Norton Antivirus. WP blocked

  some SQL Agent service. The thing is, that when worm protection disabled and

  rerun the hot fix, it failed again. Did anyone faced that too?

  Finally, although the hot fix failed, the version has changed to 9.00.3054.00

  I'm not sure if this fix is responsible for DDL Trigger bug. Can you give me

  a solution please or can you point me a hot fix for the hot fix?

  Thanks in advance,

  Kostas

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  April 26, 2007 at 5:39 am

  #702572

  Kostas,

  I don't think it's the hot fix which causes this behaviour. On my test sytem I only applied SP2 no toher hotfixes.

  While the trigger works fine for CREATE and DROP database, ALTER database commands are not rolled back. Not sure if this expected behaviour or a bug, but I will do some more research.

  Markus

  [font="Verdana"]Markus Bohse[/font]

• Michas Konstantinos-132761

  Mr or Mrs. 500

  Points: 552

  More actions

  April 30, 2007 at 4:52 am

  #703239

  Thank you, for your response and time Markus.

  Kind Regards,

  Kostas

• ryan -201626

  SSC Rookie

  Points: 26

  More actions

  May 28, 2007 at 3:31 pm

  #709227

  I've experienced the same issue, where the trigger is fired, but the rollback simply rolls back my insert statement to my log table.  Any help would be appreciated.  see code below...

  Thanks

  CREATE TRIGGER ddl_log_db_level

  ON DATABASE

  FOR

  DDL_DATABASE_LEVEL_EVENTS

  AS

  DECLARE

  @data XML

  SET

  @data = EVENTDATA()

  INSERT

  INTO ddl_log

  (PostTime, [DB_Name], DB_User, Event, TSQL)

  VALUES

  (GETDATE(), DB_NAME(), CONVERT(varchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))

  ROLLBACK

  ;