Reporting on users assigned to a specific role across all portals


As part of setting up and maintaining user access to a DNN instance, it's important to know which users are assigned to specific roles. With multiple DNN portals, manually checking can be time-consuming. Querying the database directly will provide more accurate information in a far less amount of time. 


Requirements: SuperUser access to the DNN portal

NOTE: It's advisable to take a database backup before running any SQL queries. 

1. Log in to your DNN instance with SuperUser credentials. 

2. Go to Persona Bar > Settings > SQL Console.


3. Execute the following query:

SELECT DISTINCT U.Username, R.PortalID, PA.HTTPAlias FROM Users U INNER JOIN UserRoles UR ON U.UserID = UR.UserId INNER JOIN Roles R ON UR.RoleID = R.RoleID INNER JOIN PortalAlias PA ON R.PortalID = PA.PortalID WHERE R.RoleName = 'ENTER_ROLE_NAME_HERE' AND PA.IsPrimary = 1 AND (PA.CultureCode is NULL OR PA.CultureCode = 'en-US') ORDER BY U.Username

NOTE: Enter the role name into the query above where indicated. 

4. The output of the query will show you the usernames of all users assigned to that role as well as the ID and URL of the portal they are tied to. 





Please sign in to leave a comment.