Eu escrevi uma expressão CTE muito simples que recupera uma lista de todos os grupos dos quais um usuário é membro.

As regras são assim, um usuário pode estar em vários grupos e os grupos podem ser aninhados para que um grupo possa ser um membro de outro grupo e, além disso, os grupos podem ser membros mútuos de outro, então o Grupo A é um membro do grupo. B e o Grupo B também é membro do Grupo A.

Meu CTE é assim e obviamente produz recursão infinita:

            ;WITH GetMembershipInfo(entityId) AS( -- entity can be a user or group
                SELECT k.ID as entityId FROM entities k WHERE k.id = @userId
                UNION ALL
                SELECT k.id FROM entities k 
                JOIN Xrelationships kc on kc.entityId = k.entityId
                JOIN GetMembershipInfo m on m.entityId = kc.ChildID
            )

Não consigo encontrar uma solução fácil para voltar atrás nos grupos que já gravei.

Eu estava pensando em usar um parâmetro varchar adicional no CTE para gravar uma lista de todos os grupos que eu visitei, mas usar o varchar é muito grosseiro, não é?

Existe uma maneira melhor?