SQL Server 2005 Trip Wires

Description

A number of
changes and improvements have been made to SQL Server 2005. Did I really need to state that? One of the big improvements is in the management and security area. The SQL Management Object (SMO) infrastructure replaces much of what used to be accomplished
using SQL-DMO. SMO is tightly integrated with Windows Management Instrumentation (WMI). This linkage is so tight, new capabilities now exist to take advantage of the eventing taking place.

My team is also delivering live seminars as part of the “Best Of SQL Server 2005 Launch.” One of those demos is DDL. It’s a pretty simple demo, but to understand the power, you really need to look as the available events and build some automation around
them. The SQL Server 2005 books are now online. If you look at the
DDL_EVENTS hierarchy, you’ll notice we have database and server levels events we can trap. Once we trap an event, we can setup a tripwire to take the appropriate “corrective” action. [6]

In our demo, we use
DDL_DATABASE_LEVEL_EVENTS to see what is occuring on the virtual machine demo environment. Our script will detect unapproved actions and roll them back while logging the time, date, user ids and stuff to an audit table. Our script will also post a nice
little message indicating to the user that action was prohibited. In reality, you might take advantage of other automation like sending a page to a pager/cellphone, sending an email message to the DBA’s or security professionals, etc. The automation is only
limited by your imagination and programming skills.

Isn't Drop Trigger a ddl event. How did it execute without firing the trigger?

Permissions of course!!!

To drop a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

To drop a DDL trigger defined with server scope (ON ALL SERVER) requires CONTROL SERVER permission in the server. To drop a DDL trigger defined with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

Be careful with making any changes to this behavior. I don't want to get a call that someone has painted themselves into a corner. [C]

A similar DDL trigger example is provided with the AdventureWorks sample database. To obtain the example, find the Database Triggers folder in the SQL Server Management Studio Object Explorer, located under the Programmability folder of the
AdventureWorks database. Right-click ddlDatabseTriggerLog and select "Script Database Trigger as". By default, DDL trigger
ddlDatabseTriggerLog is disabled.