Search code examples
sqldotnetnuke

Query to get user roles in DNN

I need a query to see which Roles users have in DotNetNuke. I found this query but it gives the RoleID and not the name. What if there are more than one role associated to a user?

SELECT Users.FirstName, Users.LastName, Users.Email,UserRoles.RoleID
FROM UserRoles 
INNER JOIN Users ON UserRoles.UserID = Users.UserID
Solution

  • You need to include the Roles table in your query if you want the Role Names.

    SELECT Users.FirstName, Users.LastName, Users.Email, UserRoles.RoleID, Roles.RoleName
FROM UserRoles 
INNER JOIN Users ON UserRoles.UserID = Users.UserID 
INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID
WHERE (Roles.PortalID = 0)

    You also might want to include the PortalID to avoid duplicates from other portals. However I would recommend to use the DNN core functionalities do determine a user role, with the RoleController.