Tuesday, December 24, 2013

A little fun with HierarchyId

I was recently enhancing the DDL trigger that we have implemented on all of our servers. The requirement was to e-mail the team whenever a security related event came up. Here's the trick: how do we alert on just the security events without having to hard code all of them? Here's how I did it.

The first thing to realize is that all of the events that can fire a trigger are located in sys.event_notification_event_types. Each type has a parent type, which we're going to exploit. Some of these relationships are quite deep (i.e. EventA → EventB → EventC → etc), so we need some way to say "given this parent type, find all the events under it to an arbitrary depth". I chose to use the HierarchyId data type that was introduced in SQL 2008. But it's a bit of a leap from one to the other. Let's look at the code:

DECLARE @IsSecurityEvent bit;
 
WITH cte AS (

    SELECT  * ,
            CAST('/' + CAST([type] AS VARCHAR) + '/' AS VARCHAR(MAX)) AS [path]
    FROM    sys.[event_notification_event_types] AS enet
    WHERE   [parent_type] IS NULL
    UNION ALL
    SELECT  enet.* ,
            CAST(c.[path] + CAST(enet.[type] AS VARCHAR) 
               + '/' AS VARCHAR(MAX)) AS [path]
    FROM    cte AS c
    INNER JOIN sys.[event_notification_event_types] AS enet
            ON enet.[parent_type] = c.[type]
),
h as (
    SELECT *, hierarchyid::Parse([path]) AS a
    FROM cte
)
SELECT @IsSecurityEvent = case when COUNT(*) > 0 THEN 1 ELSE 0 END
FROM h AS child
INNER JOIN h AS parent
    ON child.a.IsDescendantOf(parent.a) = 1
WHERE parent.[type_name] IN 
   ( 
      'DDL_SERVER_SECURITY_EVENTS'
      , 'DDL_DATABASE_SECURITY_EVENTS'
   )
    AND child.[type_name] = @event_type;
  

So, the @IsSecurityEvent is just a flag that will ultimately tell us whether the event that made this trigger fire was a security event. In order to create the hierarchy, we'll use a recursive CTE.. There's nothing too tricky here aside from realizing that we also need to add a column that is a textual representation of the HierarchyId that we'll ultimately create. In the next CTE, I use the parse method of the HierarchyId type to create a HierarchyId from text. Lastly, we create a self-join on the CTE of a given type to all of its descendants. This makes it easy to get all of the security-related events as we only need to specify that we care about server- and database-security events and the @IsSecurityEvent will be populated accordingly.

So don't say I never got you anything nice for Christmas. Ho ho ho! 😉

No comments:

Post a Comment