I am trying to find a way to use the DNN controllers to get the following information...

I want all the Approved users of the portal that have a particular module permission.  In my case the module PermissionKey is REVIEWER_PERM and is only used by one module.  There are also 3 roles that have that permission assigned to them.

I am using DNN 5.0.1 

This SQL will get me what I want... but I would rather use DNN controller classes to get this data.  We are upgrading DNN soon and if any of the underlying database structure changes then the SQL below may break.

SELECT DISTINCT U.* FROM
Permission P JOIN
ModulePermission MP ON P.PermissionKey='REVIEWER_PERM' AND P.PermissionID=MP.PermissionID JOIN
UserRoles UR ON UR.RoleID = MP.RoleID JOIN 
Users U ON U.UserID = UR.UserID JOIN
aspnet_Users AU ON AU.UserName = U.Username JOIN
aspnet_Membership AM ON AM.UserId = AU.UserId AND AM.IsApproved = '1'
ORDER BY U.Username

Any help would be appreciated.  I don't mind having to make several calls with multiple controller classes to accomplish my goal. This is not something I am going to be doing frequently and I can cache the results so it doesn't have to been super efficient.

Thanks, Joe

Hello,

in general you would use PermissionController to get the list of permissions in this case either of the following 2 would seem applicable:

GetPermissionsByModuleID(int moduleID)
GetPermissionByCodeAndKey(string permissionCode, string permissionKey)

Once you've got the list of permissions (in your case a list of one permission) you will have the permissionID which you can then use to track through the system - however as permissions are applied to pages (tabs) and modules this does not seem to be what you need.

What you're really trying to do is see what users belong to a role - in which case the UserController/RoleController are the approriate classes - you wont need to worry about approved/deleted users as the usercontroller will only show active/approved users

 We are upgrading as soon as possible.  A co-worker of mine is making the necessary changes so we can upgrade to latest C# version.