If you are running the latest version(s) of DotNetNuke (DNN7 or DNN8); you may have noticed that when you create security roles, those roles aren't showing up for users so they can select the roles and gain access to the pages and modules that are secured to those roles.

I thought it was about time this started working as I'm on a project where Security Roles need to be managed in a Public, Self-Service manner.

So, I talked with Sebastian Leupold (the brilliant creator of Turbo Scripts for DNN) about this DNN bug and he quickly provided a SQL script to get Security Roles (under Manage Services) showing for registered users.

First, we made sure we had each role set to "public". check...
Second, we made sure that we had enabled "Show Manage Service" in the User Account Settings. check...

First, back up your app and database (might not be a bad idea to restore that back up to a dev location to make sure it works okay); and then login as Host... and go to Host > SQL and enter in the following script:

IF EXISTS (SELECT * FROM sys.Procedures WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}GetServices]'))
DROP PROCEDURE {databaseOwner}[{objectQualifier}GetServices]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetServices]
@PortalID Int, -- pass Null for roles of all sites
@UserID Int -- not null!
AS
BEGIN
SELECT
R.*,
UR.IsOwner,
UR.UserRoleID,
UR.UserID,
UR.ExpiryDate,
UR.IsTrialUsed,
UR.EffectiveDate,
U.DisplayName,
U.Email
FROM {databaseOwner}[{objectQualifier}Users] U
INNER JOIN {databaseOwner}[{objectQualifier}UserRoles] UR ON UR.UserID = U.UserID AND (UR.UserID = @UserID OR UR.UserID IS Null)
RIGHT JOIN {databaseOwner}[{objectQualifier}Roles] R ON UR.RoleID = R.RoleID
WHERE (R.PortalId = @PortalID OR IsNull(@PortalID, -1) = -1)
AND R.IsPublic = 1
AND R.RoleId >= 0 -- DNN-4288: hide system role atm to prevent duplicates. Might be removed, after API has been adopted
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalID UNKNOWN));
END -- PROCEDURE
GO

You should see in the green bar at the top of the page that the script ran successfully... so then log out, and log back in as a register user and navigate to Manage Services and see if you can see the security roles.

tah dah!