TSQL：如何获取用户在Active Directory中所属的组列表

--; Get all groups in domain MyDomain
select  *  
from    OpenQuery(ADSI,'
    SELECT  samaccountname,mail,sn,name,cn,objectCategory
    FROM    ''LDAP://Mydomain/CN=users,DC=Mydomain,DC=com'' 
    WHERE   objectCategory=''group'' 
    ORDER BY cn
    ')

--; Get all users in domain MyDomain
select  *  
from    OpenQuery(ADSI,'
    SELECT objectCategory,department,samaccountname
    FROM ''LDAP://Mydomaindomain/CN=users,DC=com'' 
    WHERE objectCategory=''user'' 
    ORDER BY cn
    ')
--  where   samaccountname='mylogin'

我想知道的是,

如何检索MyDomain中特定用户所属的所有组的列表？

[更新]我得到了相反的结果
给定组名称,检索所有用户

select  *  
from    OpenQuery(ADSI,'SELECT objectCategory,department
    FROM ''LDAP://Mydomain/CN=users,DC=wl-domain,DC=com'' 
    WHERE MemberOf=''cn=_____GROUPNAME_____,CN=users,DC=com''
    ORDER BY cn' 
    )

解决方法

您可以检索单值属性,如“sn”(姓氏=姓氏)或“givenName”和“mail”等,但基于SQL的界面无法处理分配了多个值的“memberOf”等属性给他们.

所以我担心你不得不采取另一种方式解决这个问题 – 例如查找并填充托管代码中的组成员身份(单独在SQL Server之外,或者可能作为SQL Server中的CLR程序集).

更新：有关OPENQUERY AD提供程序限制的说明,请参阅here (MSDN Support)：

Limitations
The process of using the
OPENQUERY statement to pull
information from an LDAP server does
suffer from some limitations. The
limitations can be circumvented in
some cases,but in others the
application design must be altered. An
external application or COM object
that uses ADSI to retrieve the
information from the LDAP server and
then build a table in SQL by using ADO
or other data access methods is
another viable method.

The first limitation is that
multivalued properties cannot be
returned in the result set to SQL Server. ADSI will read schema information from the LDAP server that defines the structure and syntax of the classes and attributes used by the server. If the attribute that is requested from the LDAP server is defined in the schema as being multi-valued it cannot be returned in an OPENQUERY statement.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!