Filtering authorized users in DNN 8

Overview

DNN versions 9+ include methods to filter which registered users you see in the Users interface.  DNN versions 8 and below do not have these filters in the UI. Instead, you can use the SQL Console or a SQL IDE to run a query against your site's database to get a filtered list of your users.

Solution

In the SQL interface of your choice, you can run the following query to find authorized users only:

select Users.DisplayName as Name
,Users.Username as UserName
,Users.Email
,UserPortals.PortalID
,Users.CreatedOnDate
,users.*
from Users
INNER JOIN UserPortals ON Users.UserID = UserPortals.UserID
where 1=1
--PLACE FILTERS BELOW THIS LINE
AND UserPortals.Authorised = 1

You can replace or add to the filters by changing the code below the line that reads "PLACE FILTERS BELOW THIS LINE." Remove all filters to get all users. The table below shows the lines you can insert to create all the standard filters in DNN 9:

For example, if you wanted to get only users who were unauthorized AND deleted, you'd use the following code:

select Users.DisplayName as Name
,Users.Username as UserName
,Users.Email
,UserPortals.PortalID
,Users.CreatedOnDate
,users.*
from Users
INNER JOIN UserPortals ON Users.UserID = UserPortals.UserID
where 1=1
--PLACE FILTERS BELOW THIS LINE
AND IsDeleted = 1
AND UserPortals.Authorised = 0