3 of 3 people found this helpful
Yup, I'm right there with ya I had some Custom SQL I used to identify Site Admins and now that easy functionality is gone. Looking back at the prior Workgroup db notes in prior versions it was stated that the field was being deprecated and then depreciated and now gone
Case in point:
... -- Text value of admin level for user CASE "users"."admin_level" WHEN 5 THEN 'Site' ELSE CASE "system_users"."admin_level" WHEN 10 THEN 'Server' ELSE 'None' -- Regular user END END AS "WB Admin Level Type", ...
licensing_role_id is gone from the users table as well.
(Yeah yeah, we know that using the non-views are subject to change w/o notification (i.e. use at your own risk) but when the views that we're supposed to use are seriously lacking in information our alternatives are equally seriously lacking )
The hist_users table still contains Site Admin info. No telling how long that will last and, importantly, if it is accurate given that users.admin_level is gone (I haven't tested and don't plan to cuz I'm feeling all sorts of grumpy right now).
I had to make an update to my TS Content data source to accommodate the schema changes. You can see if what I did there will work for your purposes. Specifically, it's the "Admin Level" field under the "Owner" folder.
2 of 2 people found this helpful
Thank you for your help. Unfortunately. It doesn't help with what I want to achieve right now. We wanted to identify each user permission assigned in a site and the number of people with the respective permissions
I did have another use case built up to identify just the Admins and the non-admins. That is working fine.
I've just been playing around with the workgroup database recently. And so far, what I have found is this
There's a table called "Site_Roles". This needs to be joined with other tables as per requirement. In my case I did this with the "users", "site" and the "system_users" tables to get the full list.
If I just use the Name field from Site Roles table, it assigns a system administrator as a publisher. So I've had to use the admin level field from the "system_users" table and entered a condition ---> if admin_level=10 then "system admin" else Name (site roles)
This way, I've now been able to identify each of the site roles which we needed for our Internal purposes.
PS: Thanks for sharing the link to your workbook. Its giving me some new ideas that are worth reporting. Will probably have a lot of questions answered once I dive into it.