--; 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.
