locked
Compare UserID to a specific AD group RRS feed

  • Question

  • Here's what I need to do..

    1. I have a dataset with userID's

    2. I want to only show the records where a userID is a member of a specific AD group,

    I'm not allowed to use a linked server.  I was able to make a datasource in SSDT that connects to AD, but I don't know where to begin with the dataset expression in the SSRS report.

    Monday, June 15, 2015 12:35 PM

Answers

  • You can use the following query:

    SELECT 
    	sAMAccountName,
    	displayName,
    	mail,
    	telephoneNumber,
    	distinguishedName,
    	whenCreated,
            userAccountControl,
            memberOf
    FROM 'LDAP://OU=XXXX,dc=nwtraders,dc=msft'
    WHERE
    	objectClass='User'


    Ricardo Lacerda


    • Edited by Ricardo Lacerda Monday, June 15, 2015 4:00 PM
    • Marked as answer by nd2000 Wednesday, June 17, 2015 1:26 PM
    Monday, June 15, 2015 3:58 PM

All replies

  • You can use the following query:

    SELECT 
    	sAMAccountName,
    	displayName,
    	mail,
    	telephoneNumber,
    	distinguishedName,
    	whenCreated,
            userAccountControl,
            memberOf
    FROM 'LDAP://OU=XXXX,dc=nwtraders,dc=msft'
    WHERE
    	objectClass='User'


    Ricardo Lacerda


    • Edited by Ricardo Lacerda Monday, June 15, 2015 4:00 PM
    • Marked as answer by nd2000 Wednesday, June 17, 2015 1:26 PM
    Monday, June 15, 2015 3:58 PM
  • I don't want to pull the entire user list.  Don't I need the memberof in the where clause?  I just want to see a list of users from a specific global group.
    Monday, June 15, 2015 4:20 PM
  • Yes you do!

    First  you must create a view for bring all user and their respective group members . After that, you'll can filter the group in the where clause. 


    Ricardo Lacerda

    Monday, June 15, 2015 6:33 PM
  • Here's my query after some work...  The query runs successful but is not returning any results.  No errors, just no results.

    SELECT       sAMAccountName, displayName, distinguishedName
    FROM         'LDAP://DC=xxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx'
    WHERE        memberOf='Name of Global Group'


    If I use "company" or "sn" or any other field in my where clause it returns results.  Just not when I use a Global Group..
    • Edited by nd2000 Monday, June 15, 2015 7:04 PM
    Monday, June 15, 2015 6:58 PM
  • This maybe helpful in general...

    When I want to have access to LDAP/AD information I tend to bring that data over into my database. The data can be refreshed as often as required (daily/hourly/etc). 

    There are two big advantages to this:
    * When you make heavy use of the data, the LDAP server isn't ever swamped.
    * Writing TSQL is much easier than jumping through the various hoops LDAP forces you through.

    You could setup a couple of simple queries to grab and dump your data, perhaps users and group membership, and then do your figuring out from there.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Monday, June 15, 2015 7:31 PM
  • TRY USING THE FOLLOWING QUERY:

    SELECT * FROM 
    (
    
    	SELECT 
    		sAMAccountName,
    		displayName,
    		mail,
    		telephoneNumber,
    		distinguishedName,
    		whenCreated,
    		userAccountControl,
    		memberOf AS GROUP_MEMBER
    	FROM 'LDAP://OU=XXXX,dc=nwtraders,dc=msft'
    	WHERE
    		objectClass='User'
    ) AS Q1
    
    WHERE 
    	Q1.GROUP_MEMBER LIKE 'NAME_GROUP'


    Ricardo Lacerda

    Monday, June 15, 2015 7:51 PM
  • That doesn't work.  I keep getting syntax errors in the expression builder in SSDT.
    Wednesday, June 17, 2015 1:10 PM