Hi everyone and Lee,

I opened with interest issue Issue 33 - Professional DotNetNuke Administration using SQL (Part 2) and learned a lot. Your videos are the best!

Very much related to the issue, I want to remove the "view" access for the "All Users" role to all the pages. I think it would be great to use a SQL query to do the work, but I cannot find a role or user or anything named "All Users" in the tables.

The purpose of doing this is, this site is not rolled out yet and is underdevelopment even though there are 20 authors and 40 pages. We don't want the rest of the world to see the site yet. (They can now).

1. do you know which role or user or whatever to change the view permission for for all unregistered users?

or

2. is there an easier way to lock out the general public from seing the site when all the pages already have view permissions assigend to the "All Users" role?

Thanks Lee and everyone for your help in the forums!

Hello,

Glad it was useful!

ok, lets go through this step by step as it is slightly complicated

Go to your DNN database tables
View content of:

TabPermission - This holds the security permissions for the page

we now need to work out what the columns mean:

open

Permission - Here you can view the PermissionID and the PermissionCode
In the TabPermission table we can see they use PermissionIDs 3+4, if we look in the Permission table we can see ID3 = SYSTEM_TAB (View Tab) (so this is the view tick box column in the page settings) and ID4 = SYSTEM_TAB (Edit Tab) (so this is the edit tick box column)

View TabPermission table
View the data from one of your tabs - I'm going to look at TabID 56
I have 3 rows of data,
permission ID = 3 + Role ID = 0
permission ID = 4 + Role ID = 0
permission ID = 3 + Role ID = -1

Lets now look at RoleIDs to see what they mean

Open Roles table
first row, RoleID 0 = Administrators

So we can assume at this stage that RoleID = -1 is the 'all users' setting

BACKUP EVERYTHING

Login as Host to your DNN install
Go to Tabid 56
Go to page settings
Here we can see view page and edit page are ticked for administrators and View page is also ticked for all Users.

Go back to  TabPermission table, click on the row  with
Tab ID = 56, permission ID = 3, Role ID = -1
Press Delete to delete the row

Now go back to dnn / host menu - restart application

View page settings of TabID56 and you will see only admin have permissions and the 'all users' tick box is now unticked.

So, all we have to do now is create a query for that!

Rather than delete the RoleID I would suggest that we change it to a different security role, so when you want to re-enable the permissions to 'all users' you can just apply it to those roles with the new security role.

- Login to DNN - create a security role
I created one named 'limit access' - Go to the Roles table and view its ID, the Role ID = 4 for mine

Now go back to TabPermission table
We now want to change any rows that have RoleID=-1 to RoleID=4

So:

UPDATE TabPermission
    SET RoleID = 4
    WHERE RoleID = -1


View the TabPermssion table, you can see the results have worked correctly

Restart the DNN application
View page settings for tab ID 56

You will see 'All Users' is unticked and 'limit access' is now ticked

All you have to do now to flip it back when you release is:

UPDATE TabPermission
    SET RoleID = -1
    WHERE RoleID = 4



So, it takes a bit of working out, but the end query is very simple to implement and think of how many hours of work you have just saved resetting all the permissions! - SQL is very cool!

Cheers,

Lee