Resource User Roles DDL Trigger

How do you audit who has been added to which database roles across an entire server? This ALL SERVER DDL trigger listens for ADD_ROLE_MEMBER and DROP_ROLE_MEMBER events, parses the EVENTDATA() XML, and keeps a ResourceUserRoles table (username, rolename, created date) in sync - inserting on add, deleting on drop. The commented CREATE TABLE at the top is where the target table comes from; create it in whichever database you want the audit trail to live in. The final DROP TRIGGER line is included so you can tear it back down when the audit is over.

CREATE TRIGGER DDL_ResourceUserRoles
ON ALL SERVER
FOR ADD_ROLE_MEMBER, DROP_ROLE_MEMBER
AS
BEGIN--TRIGGER
	SET NOCOUNT ON;
	
	/*
	CREATE TABLE ResourceUserRoles
	(
		Username nVarChar(128) NOT NULL,
		Rolename nVarChar(128) NOT NULL,
		CreatedDate DateTime NOT NULL DEFAULT GetDate()
	)	
	*/
	
	DECLARE @data XML SET @data = EVENTDATA()
	
	IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') = 'ADD_ROLE_MEMBER'
	BEGIN--IF
		IF NOT EXISTS
		(
			SELECT TOP 1
				1
			FROM
				ResourceUserRoles
			WHERE
				Username = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
				AND Rolename = @data.value('(/EVENT_INSTANCE/RoleName)[1]', 'nvarchar(128)')
		)
		BEGIN--IF
			INSERT INTO ResourceUserRoles
			(
				Username,
				Rolename
			)
			SELECT
				@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
				@data.value('(/EVENT_INSTANCE/RoleName)[1]', 'nvarchar(128)')
		END--IF
	END ELSE IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') = 'DROP_ROLE_MEMBER'
	BEGIN--IF
		DELETE FROM
			ResourceUserRoles
		WHERE
			Username = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
			AND Rolename = @data.value('(/EVENT_INSTANCE/RoleName)[1]', 'nvarchar(128)')
	END--IF
END--TRIGGER
GO

DROP TRIGGER DDL_ResourceUserRoles ON ALL SERVER
GO