April 7, 2008 at 11:17 am
Hi all.
I was wondering if anyone had been able to select the memberOf column in an ldap query.
[Code]
select top 100 * FROM OPENROWSET('ADSDSOObject',
'mydomainControler.domain.local/DC=domain,DC=local;', 'SELECT cn, mail, co, distinguishedName, displayName,MemberOf
FROM ''mydomainControler.domain.local/DC=domain,DC=local'' where objectClass = ''User'' and ObjectCategory=''Person'' ')
[/Code]
Removing the MemberOf column will make the query run just fine. seam to remember there being some restrictions on openrowset functions. but not sure
Then i tried to only select users from a certain user group.
[Code]
select top 100 * FROM OPENROWSET('ADSDSOObject',
'mydomainControler.domain.local/DC=domain,DC=local;', 'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://mydomainControler.domain.local/DC=domain,DC=local'' where objectClass = ''User'' and MemberOf=''CN=[Administrators],DC=domain,DC=local''')
[/Code]
This will run but with out results. am i messing some tags in the MemberOf part of the Where clause ? if anyone has an example that i could use that would be great.
kgunnarsson
Mcitp Database Developer.
April 7, 2008 at 11:28 am
You aren't messing anything up. You just ran into a limitation of the provider being used, it doesn't support multi-valued attributes like MemberOf.
Here are a couple alternatives.
1. Redefine what you're doing, for example know ahead of time which group(s) you're working with.
2. Create a CLR stored procedure or function that uses System.DirectoryServices to return group membership.
There's a brief demonstration that shows the steps for doing that in the CLR on this post: http://qa.sqlservercentral.com/Forums/Topic452981-386-1.aspx
April 7, 2008 at 11:38 am
Thanx for the info.. What i will do is just redefine my stored procedure, so i'll filter down on user groups. I managed to find out how to use the MemberOf in a where clause.
So just if anyone gets into any problems i'll post this here.
To use the MemberOf in a where clause the best way is to first select the groups them self's.
that can be done like this
[Code]
select top 100 * FROM OPENROWSET('ADSDSOObject',
'mydomaincontroler.domain.local/DC=domain,DC=local;', 'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://mydomaincontroler.domain.local/DC=domain,DC=local'' where objectClass = ''group'' ')
[/Code]
Then copy the distinguishedName string and paste that into the Member of where clause.
In my example Administrators.
[Code]
select top 100 * FROM OPENROWSET('ADSDSOObject',
'LDAP://mydomaincontroler.domain.local/DC=domain,DC=local;', 'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://mydomaincontroler.domain.local/DC=domain,DC=local'' where objectClass = ''User'' and MemberOf=''CN=Administrators,CN=Builtin,DC=Domain,DC=local''')
[/Code]
This will provide me with an list of users that are in the Administrators group of the domain.
kgunnarsson
Mcitp Database Developer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply