TSQL：如何获取用户所属的活动目录中的组的列表 [英] TSQL: How to get a list of groups that a user belongs to in Active Diretory

问题描述

我有一个检索所有组和所有用户在一个域中的两个查询， MYDOMAIN

   - ;获取域MYDOMAIN所有组
选择	*
从OPENQUERY（ADSI
选择SAM帐户，邮件，锡，姓名，CN，objectCategory属性
从''的LDAP：// MYDOMAIN / CN =用户​​，DC = MYDOMAIN，DC = COM''
WHERE objectCategory属性=''组''
ORDER BY CN
'）

 - ;获取域MYDOMAIN所有用户
选择	*
从OPENQUERY（ADSI
选择objectCategory属性，CN，SN，邮箱，姓名，部门，Sam帐户
从''的LDAP：// Mydomaindomain / CN =用户​​，DC = MYDOMAIN，DC = COM''
WHERE objectCategory属性=''用户''
ORDER BY CN
'）
 - 在SAM帐户='mylogin
 

我想了解的是，

如何检索 MYDOMAIN 所有组的列表，一个特定的用户属于？

[更新] 我能得到相反的结果
由于组名，检索所有用户

  SELECT *
从OPENQUERY（ADSI，
选择objectCategory属性，CN，SN，邮箱，姓名，部门
从''的LDAP：// MYDOMAIN / CN =用户​​，DC = WL-域，DC = COM''
WHERE的memberOf ='CN = _____ GROUPNAME _____，CN =用户​​，DC = MYDOMAIN，DC = COM''
ORDER BY CN
）
 

解决方案

我觉得这是对T-SQL基于AD接口的限制之一 - 你无法检索多值属性，如属性（如的memberOf 用户）有多个值在其中。

您可以检索单值属性，如SN（姓=姓）和给定名称和邮件等字样，但基于SQL的接口是不能够处理像成员属性与分配给它们的几个值。

所以，恐怕你得走另一条路了这个问题 - 例如，找到并填充组成员身份管理code（另外的SQL Server，或可能与SQL Server内部的CLR程序集）。

更新：在这里看到（MSDN支持）为OPENQUERY的限制的说明AD提供商：

  

限制
使用的方法   OPENQUERY语句来拉   从LDAP服务器的信息呢   受到一些限制。该   限制可以在被规避   一些情况下，但其他在   应用设计必须改变。一个   外部应用程序或COM对象   使用ADSI来检索   从LDAP服务器信息，   然后通过使用ADO建立在SQL表   或其他数据访问方法是   另一种可行的方法。

     

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

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,

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' 
	)

解决方案

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.

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.

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

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

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.

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