locked
SQL Server LDAP QUERY to Active Directory : Search in the active directory where a user is located in a group RRS feed

  • Question

  • Good Evening,

    I'm looking for how to find a user in a group in the active directory.

    Currently I have managed to do the opposite. Find in a group that they are the users

    SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl 
    FROM OPENQUERY( ADSI, 
    'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN 
    FROM ''LDAP://DC=xxx-xxx,DC=xxx'' 
    WHERE objectCategory=''person'' 
       AND objectClass=''user''  
       AND memberOf=''CN=xxx,OU=xxxs,OU=xxx,OU=xxx,OU=Groups,OU=xxx,DC=xxx,DC=xxx'' 
    ORDER BY CN')

    • Moved by Teodora Sharkova Friday, January 13, 2017 3:04 PM moved from French MSDN forum
    Friday, January 13, 2017 2:20 PM

Answers

  • Your query will find all users that are direct members of the specified group. I think the opposite would be to find all groups that a specified user is a direct member of. For this you want to retrieve all groups where the member attribute includes a specified user. But then the attributes you retrieve must be those of a group, not a user. You are selecting givenName and sn, which are attributes of users, not groups. If you do want the groups a given user is in, perhaps something similar to below will work for you:

    SELECT sAMAccountName, cn FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, cn FROM ''LDAP://dc=xxx-xxx,dc=xxx'' WHERE objectCategory=''group'' AND member=''cn=Jim Smith,ou=Sales,ou=West,dc=MyDomain,dc=com''')
    

    Alternatively, you could retrieve the memberOf attribute of the user, but then you can only get the distinguished names of the groups. The above allows you to retrieve any attributes of the groups you want. I selected just two.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Proposed as answer by Albert_ Zhang Monday, January 16, 2017 6:37 AM
    • Marked as answer by Boujraf Hicham Monday, January 16, 2017 3:26 PM
    Friday, January 13, 2017 4:35 PM

All replies

  • Your query will find all users that are direct members of the specified group. I think the opposite would be to find all groups that a specified user is a direct member of. For this you want to retrieve all groups where the member attribute includes a specified user. But then the attributes you retrieve must be those of a group, not a user. You are selecting givenName and sn, which are attributes of users, not groups. If you do want the groups a given user is in, perhaps something similar to below will work for you:

    SELECT sAMAccountName, cn FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, cn FROM ''LDAP://dc=xxx-xxx,dc=xxx'' WHERE objectCategory=''group'' AND member=''cn=Jim Smith,ou=Sales,ou=West,dc=MyDomain,dc=com''')
    

    Alternatively, you could retrieve the memberOf attribute of the user, but then you can only get the distinguished names of the groups. The above allows you to retrieve any attributes of the groups you want. I selected just two.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Proposed as answer by Albert_ Zhang Monday, January 16, 2017 6:37 AM
    • Marked as answer by Boujraf Hicham Monday, January 16, 2017 3:26 PM
    Friday, January 13, 2017 4:35 PM
  • thank you
    Monday, January 16, 2017 3:27 PM
  • Would this help?

    xp_logininfo 'MyGroup','members'

    this should display all users in the windows group mygroup.

    Monday, January 16, 2017 3:33 PM
  • Hello,

    i don't have the relation with my computer to AD but i think you add this function

    All computer objects (objectCategory=computer)

    https://social.technet.microsoft.com/wiki/contents/articles/5392.active-directory-ldap-syntax-filters.aspx

    Wednesday, January 18, 2017 1:04 PM