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.

Add Comment
2 Answer(s)

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.

Agile Angel Answered on April 7, 2019.
Add Comment

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.

Agile User Answered on April 9, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.