SQL Query to return user details and group if user is member of group

Craig Garland 286 Reputation points
2024-04-19T06:37:54.15+00:00

Hi Guys,

So I don't write to many SQL query so there may be an easy answer to this. I need to get query multiple groups and get user details if user is member of this group then dispaly in a view.

I already have LDAP and can get a list of viewer and view working but cannot workout how to check multiple group for user.

EG:

Group 1

Member: john smith, jane doe

Group 2

Member: Jane Smith,

Results displayed.

Group1, john smith, Email, displayname,etc

Group 1, Jane Doe, email, Displayname,etc

Group 2, Jane Smith, email, displayname,etc

I see that if I was able to create an array of all user in the groups containing group and username. I could then get details for each user and then display user detail and group.

Just not sure how to do this or if there is a better method in SQL.

Thanks for your time

Regards

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,747 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Craig Garland 286 Reputation points
    2024-04-22T00:03:48.7466667+00:00

    SELECT SAMAccountName, displayName, telephoneNumber, mobile, title, givenName, sn, mail, initials, l, userAccountControl

    FROM OPENQUERY(ADSI, 

                  'SELECT userAccountControl, l, initials, mail, sn, givenName, title, mobile, telephoneNumber, displayName ,SAMAccountName

	FROM ''LDAP:///OU=USer,DC=domainDC=local,'' WHERE objectClass=''user'' AND objectCategory=''person''  and   memberof=''CN=Equity Partners,OU=group DC=domain,DC=local'' ')

               AS derivedtbl_1

    WHERE (displayName IS NOT NULL) AND (givenName IS NOT NULL) AND (l IS NOT NULL) AND (LEN(SAMAccountName) <= 3)

    The query above give me the information for one group except need to add group name to display.

    Regards

    Craig