nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

search one user's all group using SQL Server

Piper Wang_t5s1r2

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

Accepted answers

Jacques

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.

All comments

Jacques

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_t5s1r2

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_iqxvt3

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_i7jyxm

@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 :-)

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

sql

jira

jira-server

group