How to find out which enterprise Roles has users

[Samujjal_Ghosh]

Hello,

There exists many duplicate enterprise Roles in database. But there are users in some of the Roles and not in the others.

How to find out which of the roles has users and which do not?

Is there a way to generate a report that shows the roles which have users and which do not have users.

[Amit_Malik]

Hi @Samujjal_Ghosh , see if this helps .

select role_name from roles where rolekey not in (select r.rolekey from users u
JOIN user_accounts ua ON u.userkey=ua.userkey
JOIN Accounts a ON a.accountkey=ua.accountkey
JOIN role_user_account rua ON rua.accountkey = a.accountkey
JOIN roles r ON r.rolekey=rua.rolekey where r.roletype=4) and roletype=4

Thanks, Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[rushikeshvartak · Accepted solution]

-- Roles with users
SELECT r.rolekey, r.role_name
FROM roles r
WHERE EXISTS (
SELECT 1
FROM role_user_account rua
WHERE rua.rolekey= r.rolekey
) and roletype=4

-- Roles without users
SELECT r.rolekey, r.role_name
FROM roles r
WHERE NOT EXISTS (
SELECT 1
FROM role_user_account rua
WHERE rua.rolekey= r.rolekey
)and roletype=4