April 24, 2007 at 5:38 am
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
'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 in my query window, I execute an alter database statement 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
Can you point me a hot fix for the hot fix?
Thanks in advance,
Kostas
April 25, 2007 at 4:22 pm
Look into "Instead of" triggers.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 25, 2007 at 4:33 pm
Ignore that last post.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply