Recurisively get all Ldap Accounts from authorizations Table

Dec 8, 2009 at 8:43 PM

Hi,

I'm trying to build a sql query which will return all active directory accounts for a particular role authorization.

For example I can have a role defined as Budget Approver. In the Role Authorization I manage the authorizations and I assign 4 different groups, a store group StoreGroup1, an application Group AppGroup1 and an active directory security group domain\ADGroup1 and active directory user directly, domain\ADUser1.

StoreGroup1 can have for example two active directory items, domain\ADGroup2 and domain\ADUser2,

AppGroup1 can also have two active directory items, domain\ADGroup1 (could possibly repeat) and domain\ADUser3

I have to essentially write a stored procedure that given the objectSID of the from the authorization table for BudgetApprover I can retrieve a list of all corresponding active directory accounts with pemissions (authorizationtype = 1 or authorizationtype=3 ) on that item, which would be

{domain\ADGroup1,domain\ADGroup2,domain\ADUser1,domain\ADUser1,domain\ADUser3}

My sql skills are definitely lacking, it seems I should be able to use the GetStoreGroupSidMembers, GetApplicationGroupSidMembers, and ExecuteLDAPQuery but I've been staring at the queries and quite get it to work. 

Any help is greatly appreciated, 

Chris

Dec 10, 2009 at 7:12 PM

Hi,

It’s hard !

The problem is how retrieve Windows Users that are members of a given Windows Security Group.

Could be useful to know that:

1) select SUSER_SID('Domain\username') returns the User SID

2) select suser_sname(0xyour....user....sid) returns the User Name

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator  
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Dec 14, 2009 at 5:26 PM

Thanks for the response Andrea,

I started playing around with LDAP last weekend a bit and I'm starting to get the queries to work. The sql queries in the getapplicationmember and executeLDAP stored procedures are a bit beyond my sql knowledge, I thought there would be an easy way to subvert them to return all LDAP members. I'm actually happy with just getting the windows security group, I don't need to get individual members of a windows security group.

What I was looking for is a way to get all LDAP member of a store or application Group defined in NetSQLAzMan so I can send out an email, if the security group has an email associated with it I want to email the security group and not individual members.  I'm trying to reuse the security group as an email distribution group. The NetSQLAzMan framework is awesome for defining column level security on an application. So Given a current user check if user has a write to perform some action. I'm struggling with trying to reuse it from the opposite direction, given an action (or a role), get all the users who can perform it. I can do that easily enough from code, the API is great but in this case I'm constrained to do this from SQL which is harder for me.

Currently I just wrote some ad hoc LDAP queries to get the LDAP users email and objectSID from active directory and I put them in a separate table and I join on the objectSID, my group nesting is not too convoluted yet, so for role members I can look for either, LDAP, Application or Store members. If its store, I look for all LDAP type within store, if its application, I just look for all LDAP in the Application or All store groups. This is just until I pick up enough SQL to write a nice recursive query :)

If I can figure out a decent solution I will post it back here.

Thanks again,

 

Chris