TSQL：如何获取用户属于Active Directory中的组的列表 [英] TSQL: How to get a list of groups that a user belongs to in Active Directory

问题描述

我有两个查询，用于检索域中所有组和所有用户， Mydomain

I have two queries that retrieve all groups and all users in a domain, Mydomain

--; 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, cn, sn, mail, name, department,samaccountname
    FROM ''LDAP://Mydomaindomain/CN=users,DC=Mydomain,DC=com'' 
    WHERE objectCategory=''user'' 
    ORDER BY cn
    ')
--  where   samaccountname='mylogin'

我想找出的是

What I would like to find out is,

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

How do you retrieve a list of all groups in MyDomain that a particular user belongs to?

[更新] 我得到了相反的结果

给定组名，检索所有用户

[UPDATE] I was able to get the opposite result
Given the group name, retrieve all users

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

推荐答案

我认为这是基于T-SQL的局限性之一AD介面-您无法撷取多值属性，例如具有多个值的属性（例如用户的 memberOf ）。

I think this is one of the limitations of the T-SQL based AD interface - you cannot retrieve multi-valued attributes, e.g. attributes (like memberOf for the user) that have more than one value in them.

您可以检索单个有价值的属性，例如 sn（姓氏=姓氏）或 givenName和 mail等，但是基于SQL的界面无法处理为 memberOf等具有多个值的属性。

You can retrieve single-valued attributes like "sn" (surname = last name) or "givenName" and "mail" and so forth, but the SQL-based interface isn't capable of handling attributes like "memberOf" with several values assigned to them.

所以，我担心您将不得不采用另一种方法来解决此问题-例如在托管代码中查找并填充组成员身份（分别在SQL Server外部，或者可能在SQL Server内部作为CLR程序集。）。

So I'm afraid you'll have to go another way for this problem - e.g. find and populate the group membership in managed code (separately outside of SQL Server, or possibly as a CLR assembly inside SQL Server).

更新：请参阅此处（MSDN支持），以了解OPENQUERY AD提供程序的局限性：

UPDATE: see here (MSDN Support) for an explanation of limitation of the OPENQUERY AD provider:

限制
使用
OPENQUERY语句从LDAP服务器中提取
信息的过程确实会导致
受某些限制。在某些情况下，可以绕过
的限制，但在其他情况下，必须更改
的应用程序设计。
外部应用程序或COM对象
使用ADSI从LDAP服务器中检索
信息，然后
然后使用ADO
或其他数据在SQL中建立表访问方法是
另一个可行的方法。

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.

第一个限制是
多值属性不能
返回结果集中为SQL
服务器。 ADSI将从LDAP服务器读取架构
信息，其中
定义了
的结构和语法以及
服务器使用的类和属性。如果从LDAP服务器请求的
属性是在模式中定义为
多值的
，则不能在
的OPENQUERY语句中返回它。

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.

这篇关于TSQL：如何获取用户属于Active Directory中的组的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！