Using OpenQuery() to enumerate members in a security group

Using OpenQuery() to enumerate members in a security group

Post by Mikael H?ggre » Fri, 01 Feb 2002 23:01:43



I would like to perform the equivalent of the following VB script from SQL
using OpenQuery():

    set group =
GetObject("LDAP://mydomain.com/CN=MyAppUsers,DC=mydomain,DC=com")
    for each user in group.Members
        wscript.echo user.cn
    next

Searching users in the builtin group 'Users' works fine:

    select * from OpenQuery(ADSI, 'SELECT cn FROM
''LDAP://CN=Users,DC=mydomain,DC=com'' WHERE objectCategory=''person'' AND
objectClass=''user'' ')

But when trying the following on the Security group MyAppUsers.

    select * from OpenQuery(ADSI, 'SELECT cn FROM
''LDAP://CN=MyAppUsers,DC=mydomain,DC=com'' WHERE objectCategory=''person''
AND objectClass=''user'' ')

I get an empty recordset. I tried to rule out security issues by executing
the VB script using xp_cmdshell (SQL Server runs as LocalSystem):

    master..xp_cmdshell 'cscript C:\Projects\MyAppUsers.vbs'

The above returns the list of users just fine (an ugly workaround would be
to parse the results from xp_cmdshell). This indicates something wrong with
the search string used. One thing that puzzles me is that the docs about the
SQL syntax talks about "search strings", and nothing about enumerating. Is
the problem that I try to search for users, but they are stored under
organizational units and not in the security group? If that is so, how could
I perform member enumeration?

Any help would be greatly appreciated.
Mikael