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.
My hist_users.publisher_tristate has only nulls even though the documentation says it should have 0,1,2. Anyone knows how to solve this problem (apart from opening a case with Tableau) or has similar issue?
As the admin_level column is deprecated from the users table.
Is there any way from where I can get the link of admin_level column from any other table?
Basically, I have made a check in my customized script to get the data where admin_level=5 in users table but now as the column is itself deprecated so a script is throwing an error.
Does anyone have any idea about that?
Im gonna assume you were trying to just get a list of server and site admins in 10.5?
If yes, have a look at the “Site_Roles” table. That should give you a list of all site roles. This table has been introduced since 10.3.
You could then join it with the users table to get the list of users with the specific permissions.
Thanks & Regards,
Yes, something kind of same. There are two things which I want to know: -
First one is columns admin_level is deprecated from users table in 10.5 then there might be some link in another table from where I can get the same details?
The second one is in 10.3 version if column admin_level value equals to 5 for some user then does that mean that user is site admin?
Thanks in advance.
left join _users.site_id= _sites.id
left join _users.system_User_id= _system_users.id
inner join _users.id = user.id
the I created a dimension Site Role
if [Site Role Id] = 3 then 'Explorer'
ELSEIF [Admin Level] =10 then 'Server Administrator'
ELSEIF [Site Role Id] = 2 then 'Explorer (Can Publish)'
ELSEIF [Site Role Id] = 8 then 'Unlicensed'
ELSEIF [Site Role Id] = 9 THEN 'Viewer'
ELSEIF [Site Role Id] = 11 then 'Site Administrator Creator'
ELSEIF [Site Role Id] = 0 THEN 'Site Administrator Explorer'
ELSEIF [Admin Level] = 0 and [Site Role Id] = 10 then 'Creator' else [Licensing Role Name] end
Can you share script or workbook which you are using to achieve ur requirement.
It would be good to know which version of Tableau server you are trying to replicate this on?