Hi,
First post.... I'm trying to map roles to LDAP group membership and hoped I could get some help with how to analyse this please?

A role consists of between 1 and x groups from a set of x groups.
A role can only have a particular group once.
A user may have one role pr more roles (or no role because the groups they have don't describe any role).

Simple sample data:

Role      | Group
Helpdesk  | Password Reset
Security  | Password Reset
Security  | Enable User
Reception | Enable User
Reception | Disable User

The data from the LDAP is of the form

User    | Group
SmithA  | Password Reset
SmithA  | Enable User
BloggsJ | Password Reset
MouseM  | Enable User
MouseM  | Disable User

From that you can calculate the users have the following roles

User    | Role
SmithA  | Security
BloggsJ | Helpdesk
MouseM  | Reception

I'm struggling with the SQL needed to build a list of User's Roles and have a horrible feeling I'm missing the obvious. I've tied myself in knots with joins and unions but they're going nowhere.

P.S. I've asked this question on two other forums and so far nobody has been able to (or has chosen not to) give a proper answer.
If the question's not clear please do say.

Thanks

Solved it....I think...amazing what hot water on your head in the shower does.  
a) count the number of rows for each role type from the roles table.  
b) do the join on group between the role and LDAP table.  
c) count the number of times a distinct user appears per role.  
d) if that count is the same as the count of groups per role then it's a match.  

I'll code it up later and see if it works, got to tile a roof now.

Part of the problem is that I was trying to do something impossible and/or unwarranted which was to isolate the "biggest" role a user had, by that I mean if they were a member of a role who's groups were a superset of another role then I was trying to only extract the superset role....not required!

• Proposed as answer by Satheesh VariathMicrosoft community contributor 1 hour 36 minutes ago

Solved it....I think...amazing what hot water on your head in the shower does.  
a) count the number of rows for each role type from the roles table.  
b) do the join on group between the role and LDAP table.  
c) count the number of times a distinct user appears per role.  
d) if that count is the same as the count of groups per role then it's a match.  

I'll code it up later and see if it works, got to tile a roof now.

Part of the problem is that I was trying to do something impossible and/or unwarranted which was to isolate the "biggest" role a user had, by that I mean if they were a member of a role who's groups were a superset of another role then I was trying to only extract the superset role....not required!

• Proposed as answer by Satheesh VariathMicrosoft community contributor Saturday, January 25, 2014 9:52 AM

Solved it....I think...amazing what hot water on your head in the shower does.  
a) count the number of rows for each role type from the roles table.  
b) do the join on group between the role and LDAP table.  
c) count the number of times a distinct user appears per role.  
d) if that count is the same as the count of groups per role then it's a match.  

I'll code it up later and see if it works, got to tile a roof now.

Part of the problem is that I was trying to do something impossible and/or unwarranted which was to isolate the "biggest" role a user had, by that I mean if they were a member of a role who's groups were a superset of another role then I was trying to only extract the superset role....not required!

• Proposed as answer by Satheesh VariathMicrosoft community contributor Saturday, January 25, 2014 9:52 AM

DECLARE @Role TABLE
(
RoleName VARCHAR(25),
GroupName VARCHAR(30)
)

DECLARE @Ldap TABLE
(
UserName VARCHAR(25),
GroupName VARCHAR(30)
)

INSERT INTO @Role VALUES('Helpdesk', 'Password Reset'), ('Security', 'Password Reset'), ('Security','Enable User'),
('Reception','Enable User'), ('Reception', 'Disable User')

INSERT INTO @Ldap VALUES ('SmithA', 'Password Reset'), ('SmithA', 'Enable User'), ('BloggsJ', 'Password Reset')
, ('MouseM', 'Enable User'), ('MouseM', 'Disable User')

SELECT DISTINCT RoleName 
	, STUFF((SELECT ', '+GroupName 
			 FROM @Role S2 
			 WHERE S1.RoleName = S2.RoleName
			 FOR XML PATH('')), 1, 1, '') AS GroupName
FROM @Role S1

;WITH CTE_Roles AS
(SELECT DISTINCT RoleName 
	, STUFF((SELECT ', '+GroupName 
			 FROM @Role S2 
			 WHERE S1.RoleName = S2.RoleName
			 FOR XML PATH('')), 1, 1, '') AS GroupName
FROM @Role S1)
, CTE_LDap AS
(SELECT DISTINCT UserName 
	, STUFF((SELECT ', '+GroupName 
			 FROM @Ldap S2 
			 WHERE S1.UserName = S2.UserName
			 FOR XML PATH('')), 1, 1, '') AS GroupName
FROM @Ldap S1)
SELECT a.UserName, b.RoleName
FROM CTE_LDap a
INNER JOIN CTE_Roles b ON a.GroupName = b.GroupName

I hope that helps..

Good Luck :)

Visit www.sqlsaga.com for more t-sql related code snippets and BI related how to's

Solved it....I think...amazing what hot water on your head in the shower does.  
a) count the number of rows for each role type from the roles table.  
b) do the join on group between the role and LDAP table.  
c) count the number of times a distinct user appears per role.  
d) if that count is the same as the count of groups per role then it's a match.  

I'll code it up later and see if it works, got to tile a roof now.

Part of the problem is that I was trying to do something impossible and/or unwarranted which was to isolate the "biggest" role a user had, by that I mean if they were a member of a role who's groups were a superset of another role then I was trying to only extract the superset role....not required!

I see a problem with that becuase, what if there is role with 2 groups and another with the same. Which one would you map it to?

Hi, Thanks for your reply, it's introduced me to two new concepts, XML PATH to build a comma separated field of values and Common Table Expressions (I think in the past I did that using temp tables).

BUT.......

The code assumes a user only has one role!!!! 

I think it also assumes the order the query sees the groups for a user is the same as the order of the groups it sees for a role.

Maybe you (or another contributor) can help with further please?

P.S. My original idea won't work :(  logic error

• Edited by ollya123 20 hours 16 minutes ago

Hi, Thanks for your reply, it's introduced me to two new concepts, XML PATH to build a comma separated field of values and Common Table Expressions (I think in the past I did that using temp tables).

BUT.......

The code assumes a user only has one role!!!! 

I think it also assumes the order the query sees the groups for a user is the same as the order of the groups it sees for a role.

Maybe you (or another contributor) can help with further please?

P.S. My original idea won't work :(  logic error

Can you phrase the question more or shed some more input... Didn't understand much from what you said.

Can you phrase the question more or shed some more input... Didn't understand much from what you said.

Sure, no problem, if I understand correctly the code you have posted builds a string of all groups a user is in.

This is then joined against a string of all the groups that make a role....that's absolutely fine if the user is only in one role .......but they're not :(