SQL TSQL：如何获取用户在 Active Directory 中所属的组列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1766061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL: How to get a list of groups that a user belongs to in Active Directory
提问by dance2die
I have two queries that retrieve all groups and all users in a domain, Mydomain
我有两个查询检索域中的所有组和所有用户， 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 MyDomainthat a particular user belongs to?
如何检索MyDomain特定用户所属的所有组的列表？
[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'
)
回答by Beau
Stored procedure below, execute using example:
下面的存储过程，使用示例执行：
Get_ADGroups_ForUser'Beau.Holland' --AccountName
Get_ADGroups_ForUser'Beau.Holland' --AccountName
Note: replace LDAP://DC=Domain,DC=local with your own domain.
注意：将 LDAP://DC=Domain,DC=local 替换为您自己的域。
CREATE PROCEDURE dbo.Get_ADGroups_ForUser
(
@Username NVARCHAR(256)
)
AS
BEGIN
DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)
-- Find the fully qualified CN e.g: CN=Beau Holland,OU=Users,OU=Australia,OU=NSO,OU=Company,DC=Domain,DC=local
-- replace "LDAP://DC=Domain,DC=local" with your own domain
SET @Query = '
SELECT @Path = distinguishedName
FROM OPENQUERY(ADSI, ''
SELECT distinguishedName
FROM ''''LDAP://DC=Domain,DC=local''''
WHERE
objectClass = ''''user'''' AND
sAMAccountName = ''''' + @Username + '''''
'')
'
EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT
-- get all groups for a user
-- replace "LDAP://DC=Domain,DC=local" with your own domain
SET @Query = '
SELECT cn,AdsPath
FROM OPENQUERY (ADSI, ''<LDAP://DC=Domain,DC=local>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));cn, adspath;subtree'')'
EXEC SP_EXECUTESQL @Query
END
GO
回答by Hilbert Blank
You can achieve this by fetching all groups that contain the user in their member attribute, or better the user's LDAP path (distinguishedName). Here's a simple procedure doing that job.
您可以通过获取在其成员属性中包含用户的所有组，或者更好地获取用户的 LDAP 路径 (distinguishedName) 来实现此目的。这是完成这项工作的简单程序。
CREATE PROCEDURE dbo.GetLdapUserGroups
(
@LdapUsername NVARCHAR(256)
)
AS
BEGIN
DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)
SET @Query = '
SELECT @Path = distinguishedName
FROM OPENQUERY(ADSI, ''
SELECT distinguishedName
FROM ''''LDAP://DC=domain,DC=com''''
WHERE
objectClass = ''''user'''' AND
sAMAccountName = ''''' + @LdapUsername + '''''
'')
'
EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT
SET @Query = '
SELECT name AS LdapGroup
FROM OPENQUERY(ADSI,''
SELECT name
FROM ''''LDAP://DC=domain,DC=com''''
WHERE
objectClass=''''group'''' AND
member=''''' + @Path + '''''
'')
ORDER BY name
'
EXEC SP_EXECUTESQL @Query
END
-- Hilbert
——希尔伯特
回答by marc_s
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 memberOffor the user) that have more than one value in them.
我认为这是基于 T-SQL 的 AD 接口的限制之一 - 您无法检索多值属性，例如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.
您可以检索诸如“sn”（姓氏 = 姓氏）或“givenName”和“mail”等单值属性，但基于 SQL 的界面无法处理诸如“memberOf”之类的属性，并分配了多个值给他们。
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).
因此，恐怕您将不得不采用另一种方式来解决此问题 - 例如，在托管代码中查找并填充组成员身份（单独在 SQL Server 外部，或者可能作为 SQL Server 内部的 CLR 程序集）。
UPDATE:see here (MSDN Support)for an explanation of limitation of the OPENQUERY AD provider:
更新：有关 OPENQUERY AD 提供程序限制的说明， 请参见此处（MSDN 支持）：
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 thatmultivalued properties cannot bereturned in the result setto 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.
限制
使用 OPENQUERY 语句从 LDAP 服务器提取信息的过程确实受到一些限制。在某些情况下可以规避这些限制，但在其他情况下必须更改应用程序设计。使用 ADSI 从 LDAP 服务器检索信息，然后使用 ADO 或其他数据访问方法在 SQL 中构建表的外部应用程序或 COM 对象是另一种可行的方法。第一个限制是该多值属性无法在结果集返回到SQL Server。ADSI 将从定义服务器使用的类和属性的结构和语法的 LDAP 服务器读取模式信息。如果从 LDAP 服务器请求的属性在架构中定义为多值，则无法在 OPENQUERY 语句中返回。
回答by Geoffrey Dawson
Actually, retreiving the list of all groups to which a user belongs is not as straight-forward / easy as it seems. As far as I know neither PowerShell nor other scripts can deliver completely accurate results, even when retrieving the tokenGroups attribute, because in order to make this determiantion, one also has to consider membership in Builtin Groups, which are domain specific.
实际上，检索用户所属的所有组的列表并不像看起来那么直接/容易。据我所知，PowerShell 和其他脚本都不能提供完全准确的结果，即使在检索 tokenGroups 属性时也是如此，因为为了进行这一确定，还必须考虑内置组的成员身份，这些组是特定于域的。
There is a very useful thread on ActiveDirSec.org that I think you might find useful - How to enumerate the list of all Active Directory domain security groups that a user belongs to?
ActiveDirSec.org 上有一个非常有用的线程，我认为您可能会发现它很有用 -如何枚举用户所属的所有 Active Directory 域安全组的列表？
In my experience, I have learnt that this isn't as easy as it seems, and unless you have a way to verify the output for sure, there is also no way to know if your script is delivering the right results.
根据我的经验，我了解到这并不像看起来那么容易，除非您有办法确定地验证输出，否则也无法知道您的脚本是否提供了正确的结果。
回答by Raj More
The Microsoft Technet Script Centeris a great resource for scripts
在微软TechNet脚本中心是脚本一个很好的资源
http://technet.microsoft.com/en-us/scriptcenter/default.aspx
http://technet.microsoft.com/en-us/scriptcenter/default.aspx
Here is a script that claims to give out exactly what you want:
这是一个声称可以准确给出您想要的内容的脚本：
http://gallery.technet.microsoft.com/ScriptCenter/en-us/ab5400e2-489a-4738-9b85-508bcb5b75f8
http://gallery.technet.microsoft.com/ScriptCenter/en-us/ab5400e2-489a-4738-9b85-508bcb5b75f8
