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! 😉