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屋！