List of Users and Their Roles (SQL Statement)

The following SQL statement can be used to return a list of all users and the roles assigned to each user.

SELECT

R.MM405_USER_NAME
Role_Desc
, MM406_ROLE_ID
Role_Id
, MM406_USER_ID
User_Id
, U.MM405_USER_NAME
User_Name
, U.MM405_RETIRE
Retired

FROM  MM406_USER_ROLES
JOIN MM405_USER_MASTER_EXT R
ON R.MM405_USER_ID = MM406_ROLE_ID
AND MM405_USER_TYPE = 'RT'
JOIN MM405_USER_MASTER_EXT U
ON U.MM405_USER_ID = MM406_USER_ID
AND U.MM405_USER_TYPE = 'UT'
ORDER BY
R.MM405_USER_NAME ASC
, U.MM405_USER_NAME ASC

*Content contributed by Ticket Enterprises Limited

Related articles

Comments

3 comments

Date Votes
  • Darren Ison

    Extremely helpful, as usual!

    0 upvotes

  • Chris Hunjas

    Very helpful!  Wouldn't it be great if UB could just put a field on the Users screen for the role they are in?

    3 upvotes

  • Lee Tickett (Tickett Enterprises Limited)

    Glad this ancient piece of SQL has come in handy :) I guess the challenge with displaying in a view is the one-to-many relational nature of the data

    0 upvotes

Please sign in to leave a comment.