I’m trying to get users that are part of groups, but groups can be members of groups as well. So the question is about who has “effective” access to certain groups. I’ve seen many examples of using CTE on SQL Server and this seems like the same case, but all the examples are pretty simple about a single table like an org chart. In my case, I have to join multiple tables and just can’t grasp the whole recursion thing.

The tables are:

usergroup: what users have which groups

uid int
gid int

groupgroup: which groups give which groups

parentid int
childid int

group: many groups are in a single sourceid.

gid int
sourceid int
name varchar

Where the difference between parentid and childid is that parentid GRANTS childid. So if you are a member of parentid , you have effective access to childid. As expected, parentid and childid WILL EXIST in the group table.

My goal is to simply get a list of all users that have access to which groups in a given source (sourceid). Without the nesting, it would be simple for sourceid of 5:

select *
from usergroup
inner join group on group.gid = usergroup.gid
where group.sourceid = 5

But the issue is that a user could have a parent gid that gives a childid via any level of nesting, and those childids can be a gid in the sourceid of 5.

At a basic level, I just need to know users XYS have groups ABC in the source either directly OR via nesting and a flag or other marker if it’s nesting vs direct. If we could get something like a level (0 = direct, 1 = one level up, etc…) that’s all the better.

I can do this in Java code by making a big map/list and looping, but is there an elegant way to get it from SQL Server directly and perhaps faster than in code?

Thank you!