TSQL：如何获取用户在Active Directory中所属的组列表

Question

我有两个查询，分别用于检索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'

我想了解的是，
如何检索特定用户所属所有组的列表在MyDomain中？
[更新]我已经能够获得相反的结果
给定组名，检索所有用户。

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

- dance2die

Answer 1

以下是存储过程，使用示例：

Get_ADGroups_ForUser 'Beau.Holland' --AccountName

注意：请将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

- Beau

---

这是一个很棒的答案，值得更多的投票。理想情况下，你可以在子查询中获取路径，但无论如何这个方法都很有效。 - Elias

@Elias - 同意。那个子树后缀非常有用。对于其他感兴趣的人，可以在http://support2.microsoft.com/kb/187529找到更多关于设置此类事物的信息。 - Ian Yates

Answer 2

你可以通过获取所有包含用户在其成员属性中的组，或者更好地获取用户的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 Blank

Answer 3

我认为这是基于T-SQL的AD接口的一个限制，你无法检索多值属性，例如像用户的"memberOf"（成员）这样有多个值的属性。

你可以检索单值属性，如"sn"（姓氏=姓）或"givenName"和"mail"等，但基于SQL的接口无法处理像"memberOf"这样具有多个值的属性。

所以恐怕您需要采取其他方法解决此问题，例如在托管代码中查找和填充组成员身份（与SQL Server分开或可能作为SQL Server内部的CLR程序集）。

更新：请参见此处（MSDN支持）以了解OPENQUERY AD提供程序的限制说明：

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

第一个限制是无法在结果集中返回多值属性到SQL Server。 ADSI将从LDAP服务器读取模式信息，该信息定义服务器使用的类和属性的结构和语法。如果从LDAP服务器请求的属性在模式中被定义为具有多个值，则无法在OPENQUERY语句中返回它。

- marc_s

---

我决定了解这个问题的原因是因为，我能够做到完全相反的事情——根据组名，检索属于该组的所有用户。（为此更新了问题） - dance2die

是的，因为那是所有单值条目的列表。用户的“memberOf”是一个多值属性，有多个条目（这与关系设计中的第一范式完全相反）。 - marc_s

通过您的查询，您将获得AD中用户对象的列表 - 对于每个用户对象，您只会访问和使用单值属性（如cn、sn、objectCategory等）。 - marc_s

看起来我需要通过创建一个CLR函数或存储过程来改变策略。谢谢，marc_s。 - dance2die

Answer 4

实际上，获取用户所属所有组的列表并不像看起来那么直接/简单。据我所知，无论是PowerShell还是其他脚本，即使检索tokenGroups属性，也无法提供完全准确的结果，因为为了做出这个决定，还必须考虑内置组的成员资格，这些组是特定于域的。

在ActiveDirSec.org上有一个非常有用的主题，我认为您可能会发现它很有用-如何枚举用户所属的所有Active Directory域安全组的列表？

根据我的经验，我已经学到了这并不像看起来那么容易，除非您有一种验证输出的方法，否则也没有办法知道您的脚本是否提供了正确的结果。

- Geoffrey Dawson

Answer 5

你可以在 MS SQL Server 上使用以下代码：

EXEC master..xp_cmdshell 'dsquery user -samid username | dsget user -memberof'

它将返回该用户名的所有组

- Sergey Ivannikov

Answer 6

Microsoft Technet Script Center 是一个很棒的脚本资源。

http://technet.microsoft.com/en-us/scriptcenter/default.aspx

这里有一个脚本声称可以精确地给出你想要的：

http://gallery.technet.microsoft.com/ScriptCenter/en-us/ab5400e2-489a-4738-9b85-508bcb5b75f8

- Raj More

---

@Raj：感谢您提供的这些链接。我已经查看了许多脚本，我能够通过编程实现，比如使用C#或PowerShell，但我无法将它们转换为TSQL中的“LDAP”查询。 - dance2die