Guillaume's answer was so close that I was able to fill in the missing pieces. In case anyone finds this later, summary changes:

• The rolehierarchy view was the key and great to show the breadcrumbs as a plus. I can see that being used elsewhere. But I needed the toplevel bit value so I added that to the view.

• I split the roles and groups into different columns in the rolehierarchy view. No big difference to the solution, but it's easier for us to have those split out.

• The main query then needed roles/groups split and the toplevel in the searches.

• Changed the GROUP BY to include the toplevel. Since it was a bit value, used ISNULL(MAX(CAST(toplevel AS INT)),0) AS toplevel to determine if a toplevel role was in the hierarchy somewhere.

• I added a lot more mess to the sample data to verify. Toplevel Role A now gives 5 levels deep of sub-roles, and non-toplevel Role C also gives many subroles and groups.

I have it very nearly complete in Updated DB<>Fiddle.

In the last final result, I have Alice's full access and whether it is direct or under a toplevel. But I can't have a HAVING clause to filter only those toplevel = 0. Does anyone know how to do that?

Thank you all.