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

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

空虚的领带 · 元素定位之css ...· 1 年前 ·

大气的芒果 · selenium ...· 1 年前 ·

面冷心慈的菠萝 · Pandas 向csv文件追加列 - 知乎· 1 年前 ·

豪情万千的麻辣香锅 · JS - 根据数组中对象的某个属性进行排序 ...· 1 年前 ·

瘦瘦的海豚 · Codespaces ...· 1 年前 ·

腾讯云

开发者社区

文档意见反馈控制台

首页 TVP

文章/答案/技术大牛

发布

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

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

CREATE PROCEDURE dbo.GetLdapUserGroups
    @LdapUsername NVARCHAR(256)
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

CREATE PROCEDURE dbo.Get_ADGroups_ForUser
    @Username NVARCHAR(256) 
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

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

5 个回答