How to find roles given to users in Share tab
Does any body have a sql script to find the roles given to all users in the ‘Share’ of the user profile tab., search or out of the box report?
I need to find the distinct users given a PPM role for licensing.
Role assignments are listed in the USER_ASSIGNMENT table where CLASS = 11640 (group = 11885, site = 11760). USER_ID links to AGILEUSER.ID and then you get the name of the role (only where CLASS = 11640) from NODETABLE. If roles are granted through the user group, then you need to link through USER_ASSIGNMENT to the group, and then to the roles which it possesses.
Although the “Share” tab does show roles, it does not apparently link to USER_ASSIGNMENT. According to the admin guide, it is “automatically populated, and does not have fields or properties”. So I have no idea how or under what conditions it is populated.
A query to list all user roles would be :
select u.loginid, u.first_name, u.last_name, n.description “ROLE”
from agileuser u, user_assignment ua, nodetable n
where ua.user_id = u.id and ua.objectid = n.id and ua.class = 11640
order by u.loginid, n.description;
If you know the name of the PPM role that you need to know who has, add “and n.description = ‘<role name>’ ” to the where clause, and it will only show you those users who have that specific role.
Kevin,
Thank you for the query. I was able to find a report named ‘User Share Lists For Granted ACL’ which list the roles ‘Shared’ to a user. Your query allows me to very the results in the report.