SQL Server 2005 Trip Wires

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 http://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 http://blogs.technet.com/keithcombs/default.aspx