none
sql query to get all users of a particular Active Directory group

    Question

  • Hi All,

    I wanted to write a sql query to return all the users from a specific AD Group specifying the Group Name instead of the CN value in memberof.

    I have written the query as shown below. kIndly help me on the same :

    SELECT sAMAccountName as Login 
    FROM OPENQUERY( ADSI, 
    'SELECT sAMAccountname FROM ''LDAP:// DC=<<MyDomainName>>,DC=com'' 
    WHERE objectCategory=''person'' AND objectClass=''user''  AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''')

    Kindly help me on the same

    Thanks


    ram

    Friday, January 4, 2013 10:22 AM

Answers

  • Hi coolram4u,

    You can try the following command:

    select *
    from sys.syslogins 
    where loginname like 'ADGroupName\%'
    


    Allen Li
    TechNet Community Support

    Monday, January 7, 2013 2:31 AM
    Moderator

All replies

  • Hi All,

    I wanted to write a sql query to return all the users from a specific AD Group specifying the Group Name instead of the CN value in memberof.

    I have written the query as shown below. kIndly help me on the same :

    SELECT sAMAccountName as Login 
    FROM OPENQUERY( ADSI, 
    'SELECT sAMAccountname FROM ''LDAP:// DC=<<MyDomainName>>,DC=com'' 
    WHERE objectCategory=''person'' AND objectClass=''user''  AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''')

    Kindly help me on the same

    Thanks


    ram

    Friday, January 4, 2013 10:23 AM
  • The trick in this URL is for OpenRowSet. But typically you can do the same for OPENQUERY.
    Saturday, January 5, 2013 8:19 PM
  • Hi coolram4u,

    You can try the following command:

    select *
    from sys.syslogins 
    where loginname like 'ADGroupName\%'
    


    Allen Li
    TechNet Community Support

    Monday, January 7, 2013 2:31 AM
    Moderator
  • You can check my article, it may help you

    http://sql-articles.com/articles/security/use-xplogininfo-to-retrieve-ad-group-members-list/


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Monday, January 7, 2013 9:21 AM
    Moderator
  • Hi Ram,

    You can use the following command to list out all the users from an AD group.

    AD Group: Domain_name\Group_Name

    --EXEC MASTER..XP_CMDSHELL 'dsquery group -name "Group_Name" | dsget group -members -expand -c | dsget user -samid -c'

    The above command will display the results as members from that AD Group. But it will show "samid" (default column name) as a user in the first row, as same as "dsget succeeded" as a user in the last but one row in the results. So you can ignore these two rows.


    Regards Chenchi MSSQL Server DBA

    Monday, January 7, 2013 1:12 PM
  • How is that an answer?
    Tuesday, August 28, 2018 5:21 PM
  • DECLARE @group NVARCHAR(128) = '<<YourGroupName>>'
    DECLARE @DC1 NVARCHAR(128) = '<<YourDomainName>>'
    DECLARE @DC2 NVARCHAR(128) = 'com'
    
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @group_dn NVARCHAR(512)
    DECLARE @result TABLE(name NVARCHAR(512))
    
    SET @SQL = 
    'SELECT distinguishedName
    FROM OPENQUERY
    (ADSI,''SELECT cn, distinguishedName, dc
    FROM ''''LDAP://DC=' + @DC1 + ',DC=' + @DC2 + '''''
    WHERE objectCategory = ''''group'''' AND cn = ''''' + @group + ''''''')'
    
    --PRINT @SQL
    INSERT @result(name)
    EXEC sp_executesql @SQL
    SELECT @group_dn = name FROM @result
    
    SET @SQL =
    'SELECT *
    FROM OPENQUERY (ADSI, ''<LDAP://' + @DC1 + '.' + @DC2 + '>;
    (&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=' + @group_dn + '));
    cn, sAMAccountName, adspath, distinguishedName;subtree'')
    ORDER BY cn;'
    
    --PRINT @SQL
    EXEC sp_executesql @SQL
    
    
    This will display not only the users in the group itself but in all the nested groups as well.


    Tuesday, August 28, 2018 5:30 PM
  • The answer from Alexander Suprun worked perfectly, thanks.

    Making IT Happen - https://blog.techygeekshome.info


    Thursday, November 15, 2018 9:09 PM