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 down at the bottom of this post for those.

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.

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. 

See the full Data Definition Language (DDL) demo to get an idea on how to do this.

The script we demo is as follows:

USE AdventureWorks;
GO

CREATE TABLE AuditDDLOperations
(
OpID int NOT NULL identity
CONSTRAINT AuditDDLOperationsPK
PRIMARY KEY CLUSTERED,
LoginName sysname NOT NULL,
UserName sysname NOT NULL,
PostTime datetime NOT NULL,
EventType nvarchar(100) NOT NULL,
DDLOp nvarchar(2000) NOT NULL
);
GO

CREATE TRIGGER PreventAllDDL
ON DATABASE
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
RAISERROR ('DDL Operations are prohibited on this production database. Please contact ITOperations for proper policies and change control procedures.', 16, -1)
ROLLBACK
INSERT AuditDDLOperations
(LoginName,
UserName,
PostTime,
EventType,
DDLOp)
VALUES (SYSTEM_USER, CURRENT_USER, GETDATE(),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
RETURN;
GO

--Test the trigger.
CREATE TABLE TestTable (col1 int);
GO

DROP TABLE AuditDDLOperations;
GO

SELECT * FROM AuditDDLOperations;
GO

--Drop the trigger.
DROP TRIGGER PreventAllDDL
ON DATABASE;
GO

DROP TABLE AuditDDLOperations;
GO

As you can see from the example and demo above, it’s a simple script but has a lot of power.  If you are interested in other examples and procedures, see the “Designing DDL Triggers” online help page.  Of course you should also have a local copy of this page since I know you were a good doggie and installed SQL Server 2005.  If you don’t have a copy, shame on you.  Come to one of the remaining launch events if you can and get one.  If registration is blocked because it is full, you can still come by.  However, walkins aren’t guaranteed to get in and you’ll only get a free copy of SQL Server 2005 and Visual Studio 2005 if you get in. 

Here are the scripts from Kai’s webcast:

--Step 1: Create a table to log audited events to
USE LucernePublishing
GO
CREATE TABLE DDL_Audit (id INT PRIMARY KEY IDENTITY,
AuditText VARCHAR(MAX))
GO

--Step 2: Create a table for testing the trigger
CREATE TABLE LPTriggerTest (id INT PRIMARY KEY)
GO

--Step 3: Create a trigger to fire when
-- an ALTER TABLE command is issued
CREATE TRIGGER LPTableAlter
ON DATABASE
AFTER ALTER_TABLE
AS
INSERT INTO DDL_Audit VALUES('A database table was altered.')
GO

--Step 4:Add a new column to the test table
ALTER TABLE LPTriggerTest
ADD TestText VARCHAR(MAX)
GO

--Step 5:Verify that the trigger fired
SELECT * FROM DDL_Audit
GO

============

--Step 1: Create a trigger to prevent
-- tables from being dropped.
USE LucernePublishing
GO

CREATE TRIGGER Safety
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You must disable the Safety trigger
before you drop a table.'
ROLLBACK
GO

--Step 2: Test the trigger by trying to drop a
-- table from the database
DROP TABLE dbo.LPBuildVersion
GO

--Step 3: Drop the trigger to allow database
-- tables to be dropped
DISABLE TRIGGER [Safety] ON DATABASE
GO

=============

--Step 1: Create a new table for auditing
--with the eventdata() function
USE LucernePublishing
GO
CREATE TABLE DDL_Eventdata (
PostTime datetime,
DB_User nvarchar(100),
Event nvarchar(100),
TSQL nvarchar(2000)
)
GO

--Step 2: Modify the trigger
-- to use eventdata() function
ALTER TRIGGER LPTableAlter
ON DATABASE
AFTER ALTER_TABLE
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO DDL_EventData
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'nvarchar(2000)') ) ;
GO

--Step 3: Modify the test table again to
-- cause the trigger to fire
ALTER TABLE LPTriggerTest
ADD TestText2 VARCHAR(MAX)
GO

--Step 4: Verify that the trigger fired and wrote
-- eventdata() information to the table.
SELECT * FROM DDL_EventData
GO