SP for parent/child relationship

Steven · September 26th, 2005, 01:37 AM

I have three tables - basically the same as sysusers, sysmembers, and syspermissions which form a parent/child relationship, however, I can't for the life of me think how I the permissions out other than from 2 levels.
This is what I'm working with now essentially:

Code:

SELECT     MAX(dbo.syspermissions.actadd)
FROM         dbo.sysmembers RIGHT OUTER JOIN
                      dbo.syspermissions ON dbo.sysmembers.groupuid = dbo.syspermissions.grantee
WHERE     (dbo.syspermissions.grantee = 9) AND (dbo.syspermissions.id = 5) OR
                      (dbo.syspermissions.id = 5) AND (dbo.sysmembers.memberuid = 9)

So this will get me the maximum permissions on ObjectID = 5 for user id = 9 that have been granted to either the user themselves, or any group they are explicitly in.

For example, if UserA is a member of GroupB and GroupB has higher permissions, GroupB's permissions will be returned.

However, If GroupB is a member of GroupC, I can't get that work.

I could have a query along the lines of:

Code:

SELECT     MAX(dbo.syspermissions.actadd)
FROM         dbo.sysmembers sysmembers_1 RIGHT OUTER JOIN
                      dbo.sysmembers ON sysmembers_1.groupuid = dbo.sysmembers.memberuid RIGHT OUTER JOIN
                      dbo.syspermissions ON dbo.sysmembers.groupuid = dbo.syspermissions.grantee
WHERE     (dbo.syspermissions.grantee = 9) AND (dbo.syspermissions.id = 5) OR
                      (dbo.sysmembers.memberuid = 9) AND (dbo.syspermissions.id = 5) OR
                      (sysmembers_1.memberuid = 9) AND (dbo.syspermissions.id = 5)

but I'd rather have something which ran against itself, so I wouldn't have to explicitly state the number of levels

Anyone have any ideas?

I am a loud man with a very large hat. This means I am in charge