Find Rules Assigning Inactive Users

These queries will find rules that set fields with user prompts or Reader queues where the user is inactive or missing.

User Fields (i.e. Staff Assigned)

SELECT r.[id]
    ,r.[name]
    ,r.[folder]
    ,r.[user]
    ,u.*
FROM (
    SELECT [xml].value('(p[k="prompt"]/v)[1]', 'varchar(64)') AS [user]
        ,*
    FROM [rule]
    WHERE 1 = 1
        AND [xml].value('(p[k="field"]/v)[1]', 'varchar(250)') = 'user'
        AND [active] = 1
    ) r
LEFT JOIN [user] u
    ON r.[user] = u.[user]
WHERE u.[active] = 0
    OR u.[id] IS NULL

Reader Queues

SELECT r.[id]
    ,r.[name]
    ,r.[folder]
    ,r.[user]
    ,u.*
FROM (
    SELECT [xml].value('(p[k = "queue_user"]/v)[1]', 'varchar(64)') AS [user]
        ,*
    FROM [rule]
    WHERE 1 = 1
        AND [xml].exist('p[k = "queue_user"]/v') = 1
        AND [active] = 1
    ) r
LEFT JOIN [user] u
    ON r.[user] = u.[id]
WHERE u.[active] = 0
    OR u.[id] IS NULL