 |
|
Blog
|
|
|
|
|
|
Mar30Written by:slhilbert
3/30/2009 11:12 AM  Matt asked on Twitter how he could find all of the roles on his Dotnetnuke site that were not currently being used at either the page or module level. You can find this information out by doing the following: - Login to your DNN site with a user with Host Permissions.
- Navigate to Host and then select SQL.
- In the multi-line textbox paste this SQL Query paste the following code
select distinct(Roles.RoleID), Roles.PortalID, Roles.RoleName from roles left Join TabPermission on Roles.RoleID = TabPermission.roleid Where TabPermission.RoleID Is NULL
union
select distinct(Roles.RoleID), Roles.PortalID, Roles.RoleName from roles inner join ModulePermission on Roles.RoleID = ModulePermission.roleid where ModulePermission.RoleID is null UPDATE: Sebastian Leupold has commented saying that the below query would work better and since I just named my son Sebastian, I have to believe that he is right :) SELECT DISTINCT R.RoleID, R.PortalID, R.RoleName from {databaseOwner}{objectQualifier}Roles R
LEFT JOIN {databaseOwner}{objectQualifier}TabPermission T on R.RoleID = T.RoleID LEFT JOIN {databaseOwner}{objectQualifier}ModulePermission M on R.RoleID = M.RoleID
WHERE T.RoleID Is Null AND M.RoleID IS Null
- Click the “Execute” button.
- The results shown are the DNN Roles that are NOT being used.
Technorati Tags: Dotnetnuke,module,Host,Permissions,Query,code,RoleID,PortalID,RoleName,Join,TabPermission,NULL,ModulePermission,results,Roles 11 comment(s) so far...
Re: Finding All Dotnetnuke Roles Not Being Used
Don't you love Twitter? Instant answers, thanks Stuart!
By Tom Kraak on
3/30/2009 11:13 AM
|
Re: Finding All Dotnetnuke Roles Not Being Used
I do LOVE Twitter. It has really revolutionized the way I work. I haven't turned my Twittering into a profit yet, but I think in time that will happen.
By slhilbert on
3/30/2009 11:15 AM
|
Re: Finding All Dotnetnuke Roles Not Being Used
That worked like a charm. I'm in the process of trimming down the number of roles we have because about 100 of them are repeats of something by a lazy admin.
thanks!
By M@ on
3/30/2009 12:55 PM
|
Re: Finding All Dotnetnuke Roles Not Being Used
Sorry, Stuart, but your solution is not quite correct, because you are adding all roles without tabpermission to all roles without modulepermissions, resulting in A \ B UNION A \ C = A \ (B intersect C), i.e. all roles, not having both, tabpermissions AND modulepermissions. A correct approach would be
SELECT RoleID, PortalID, RoleName FROM {databaseOwner}{objectQualifier}roles
WHERE RoleID NOT IN (SELECT RoleID FROM {databaseOwner}{objectQualifier}TabPermissions)
AND RoleID NOT IN (SELECT RoleID FROM {databaseOwner}{objectQualifier}ModulePermissions)
By Sebastian Leupold on
3/30/2009 1:47 PM
|
Re: Finding All Dotnetnuke Roles Not Being Used
Sebastian,
I have tried my version and your version and I am getting the same result. Thanks for query, I will include it with mine so people have choices.
Stuart
By slhilbert on
3/30/2009 1:53 PM
|
Re: Finding All Dotnetnuke Roles Not Being Used
Stuart,
I haven't had the chance to test my statement and found two issues, corrected subsequently ( one was a spelling issue, the other was to exclude Null values from subquery, which results in unexpected behaviour:
SELECT RoleID, PortalID, RoleName FROM {databaseOwner}{objectQualifier}Roles
WHERE RoleID NOT IN (SELECT RoleID FROM {databaseOwner}{objectQualifier}TabPermission WHERE RoleID IS NoT Null)
AND RoleID NOT IN (SELECT RoleID FROM {databaseOwner}{objectQualifier}ModulePermission WHERE RoleID IS NoT Null)
When testing it locally, I get one role no longer listed, which has module permission only and no tab permission. I prefer this solution for readability over the following fixed version of yours (which has better performance):
SELECT DISTINCT R.RoleID, R.PortalID, R.RoleName from {databaseOwner}{objectQualifier}Roles R
LEFT JOIN {databaseOwner}{objectQualifier}TabPermission T on R.RoleID = T.RoleID LEFT JOIN {databaseOwner}{objectQualifier}ModulePermission M on R.RoleID = M.RoleID
WHERE T.RoleID Is Null AND M.RoleID IS Null
Regards,
Sebastian
By Sebastian Leupold on
3/31/2009 8:35 AM
|
Re: Finding All Dotnetnuke Roles Not Being Used
Thanks Sebastian I have updated the post.
By slhilbert on
3/31/2009 9:23 AM
|
Re: Finding All Dotnetnuke Roles Not Being Used
Here's the real kicker. Is it possible to see what roles are mapped to what modules and what pages? I'd love to get a list of all that so I can combine a bunch of them. I wasn't kidding, we have almost 250 roles, all because this dimwit didn't care enough to look up stuff from before and see if we had something similar. When we changed company names, he created a whole new set.
Yes, you may shoot me now :)
Thanks!
By Matt on
3/31/2009 2:17 PM
|
Re: Finding All Dotnetnuke Roles Not Being Used
Wow, I got a WHOLE lot less this time using Sebastians query.
By Matt on
3/31/2009 2:18 PM
|
Re: Finding All Dotnetnuke Roles Not Being Used
Matt, regarding a mapping of roles, to pages, modules, etc is totally doable but would take some time. It would probably end up being a custom module of some sort. I am more than happy to discuss further, but it will probably end up costing some money.
By Stuart on
3/31/2009 2:24 PM
|
Re: Finding All Dotnetnuke Roles Not Being Used
If you want this to be a list you can access anytime without remember the SQL you could also use the report module. Just add it to a protected page, enter data source/provider, enter the same query as above and you are done. Then you can access the new page anytime you want to see a real-time list of the roles not being used at the page or module level.
By S .Shawn Mehaffie on
4/4/2009 9:59 PM
|
|
|
|
 |
|
|
Search Blog
|
|
|
|
| |
|
|
Blog Archive
|
|
|
|
| |
|