SQL Server 2005 Trip Wires

Sign in to queue


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.

One way to take advantage of this instrumentation is to use Data Definition Language (DDL) or Data Manipulation Language (DML) via T-SQL.  Last week, Kai Axford delivered a webcast on SQL Security as part of the “A More Secure and Well-Managed Infrastructure” series.  In his webcast (Part 11), he did some DDL and DML demos.  A number of people wanted me to post the scripts, so look at https://blogs.technet.com/keithcombs/archive/2005/11/21/414908.aspx 
for those scripts.

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. 

For more information, head over to my blog at https://blogs.technet.com/keithcombs/default.aspx



Download this episode

The Discussion

  • User profile image
    This is Fantastic!  Thanks Keith for putting this together.  Trip Wires are great ways to use the triggers.
  • User profile image
    matthester wrote:
    This is Fantastic!  Thanks Keith for putting this together.  Trip Wires are great ways to use the triggers.

    No problem.  Looks like we have a nice new vehicle for doing some fun demos.  I plan to do stuff related to work and play, so stick around for the hobby stuff.

    Since I didn't get an xbox360 today, it looks like I'm going to have more time on my hands to cry... [C]
  • User profile image
    Isn't Drop Trigger a ddl event. How did it execute without firing the trigger?
  • User profile image
    AIM48 wrote:
    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.

Add Your 2 Cents