Security Roles are Currently Unavailable in DNN 7.3.x

After upgrading to DNN 7.3.x, Admin > Security Roles gives an error saying it is currently unavailable.

These are NULL by default in versions up to 7.2.2 but need changed to N in 7.3.x.
 
 
 
To correct this issue, go to Host > SQL in DNN, and execute the following SQL query:
UPDATE {databaseOwner}[{objectQualifier}Portals] 
 SET AdministratorRoleId = CASE 
	WHEN Exists(SELECT * FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID AND R.RoleName Like 'Admin%') THEN
	 (SELECT MIN(RoleID) FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID AND R.RoleName Like 'Admin%')
	ELSE
	 (SELECT MIN(RoleID) FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID)
  END
 FROM  {databaseOwner}[{objectQualifier}Portals] P
 WHERE AdministratorRoleId Is Null OR AdministratorRoleId != IsNull((SELECT RoleID FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID AND R.RoleID = P.AdministratorRoleId), -1)
GO

UPDATE {databaseOwner}[{objectQualifier}Portals] 
 SET RegisteredRoleId = CASE 
	WHEN Exists(SELECT * FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID AND R.RoleName Like 'Register%') THEN
	 (SELECT MIN(RoleID) FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID AND R.RoleName Like 'Register%')
	ELSE
	 (SELECT MIN(RoleID) FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID AND RoleID > AdministratorRoleId)
  END
 FROM  {databaseOwner}[{objectQualifier}Portals] P
 WHERE RegisteredRoleId Is Null OR RegisteredRoleId != IsNull((SELECT RoleID FROM {databaseOwner}[{objectQualifier}Roles] R WHERE R.PortalID = P.PortalID AND R.RoleID = P.RegisteredRoleId), -1)
GO

UPDATE {databaseOwner}[{objectQualifier}Roles] 
 SET   [BillingFrequency] = 'N'
 WHERE IsNull([BillingFrequency], '') = '' OR [BillingFrequency] NOT IN ('D', 'M', 'N', 'O', 'W', 'Y')

UPDATE {databaseOwner}[{objectQualifier}Roles] 
 SET   [TrialFrequency] = 'N'
 WHERE IsNull([TrialFrequency], '')   = '' OR [TrialFrequency]   NOT IN ('D', 'M', 'N', 'O', 'W', 'Y')

UPDATE {databaseOwner}[{objectQualifier}Roles] 
 SET   [TrialPeriod] = 1
 WHERE [TrialPeriod] IS Null 

UPDATE {databaseOwner}[{objectQualifier}Roles] 
 SET   [BillingPeriod] = 1
 WHERE [BillingPeriod] IS Null 

UPDATE {databaseOwner}[{objectQualifier}Roles] 
 SET   [ServiceFee] = 0
 WHERE [ServiceFee] IS Null 

UPDATE {databaseOwner}[{objectQualifier}Roles] 
 SET   [TrialFee] = 0
 WHERE [TrialFee] IS Null 
GO
Once completed, run Tools > Recycle Application Domain to recycle the app pool, and then visit Admin > Security Roles to confirm you can access it now. Depending on the customers settings, the Administrators role may have a NULL value and need changed to N. Simply change Subscribers to Administrators and run again.
 

Add Feedback