search one user's all group using SQL Server

[Piper Wang]

Using administrator account, I want to search a specific member's all belonged group using SQL Server

How do I enter the query to get the user and all the groups that include him?

• e.g. I'm a member in group AAA, group BBB, I want to get my name, AAA, and BBB as query outcome.

Thank you.

Piper

[Jacques · Accepted solution]

Hello Piper,

The following SQL is going to give you the users & groups. If you have more than 1 user directory configured you might see multiple identical entries. Please adapt the WHERE condition as you see fit:

SELECT cwd_group.group_name, cwd_user.user_name, cwd_user.display_name
FROM cwd_group INNER JOIN
cwd_membership ON cwd_group.ID = cwd_membership.parent_id INNER JOIN
cwd_user ON cwd_membership.child_id = cwd_user.ID
WHERE cwd_user.user_name = 'AAA'

Hope this helps!

--- Jacques. 

[Piper Wang]

Hi Jacques,

It works! Thank you.

Further, I want to combine all group into one rows if the user is same.

• e.g. I'm a member in group AAA, group BBB, I want to output like:

          name       group

          Piper      AAA,BBB

Is there any way to solve this?

Thank you!

[RichardA]

Hi @Piper Wang , We are also working on the same requirement. Do you have any sql query/script for this to search the user name and group information.

Thanks !

[Håkan Berg]

@Piper Wang @RichardA - old post I know, but since I found this when searching for related information I thought I can share the SQL I finally wrote to solve my issue;

select
  cu.id as "User_ID",
  cu.display_name as "Full name",
  cu.user_name as "Username",
  cu.lower_email_address as "Email",
  cd.directory_name as "Directory",
  to_char(cu.created_date, 'YYYY-MM-DD') as "Created Date",
  cu.active,
  '"'||string_agg(cm.parent_name, ', ')||'"' as "Groups",
  (
    select
      '"'||string_agg(distinct(license_role_name),', ')||'"'
    from
      licenserolesgroup
    where
      group_id in (
        select
          cmm.parent_name
        from
          cwd_membership cmm
          join cwd_user cuu on cmm.child_name = cuu.user_name
          and cuu.id = cu.id
      )
  ) as Application
from
  cwd_user cu
  join cwd_membership cm on cu.user_name = cm.child_name
  join cwd_directory cd on cu.directory_id = cd.id
  and cu.directory_id = cm.directory_id
group by 1,2,3,4,5,6
order by cu.user_name,cd.directory_name;

This results in a list of users, directories and some other interesting data, including all Groups in a single comma-separated list instead of separate rows :-)