Are you still corresponding with Tableau Team Singapore?
Let me know if you still need support with this
The only thing I found that could help you is this, but it's not precise enough with your SQL.
When you say:
I was able to figure out the SQL to get the site roles
Did you mean you wasn't able to figure it out?
Lénaïc RIÉDINGER, Global Community Engineer Tableau
If you see a Helpful or Correct response, please mark it thanks to the buttons below the targeted post!
I don't need any more support on this. I was able to figure it out. The SQL I posted is the solution to my problem. I pasted it, so other people can use this solution if needed.
do you already know that this query doesn't work for Tableau Server 10.5?
I was using your query to get the site roles of all site users, unfortunately the column users.publisher_tristate no longer exist.
It would be great if someone could find a new solution für the same issue.
I already asked Tableau Customer Support, but the answer was that assisting with building or troubleshooting custom administrative views is out of scope of the Technical Support Organization.
At least I thought that they would point out to the new table reference or column that holds information about site role especially the SiteAdmin role, but that wasn't the case.
Maybe someone within the community has a new solution for that.
Thanks for sharing, I will look into it.
Yes I know. Thanks for checking with me. The hist_users.publisher_tristate is full of NULLs in my case and upon checking with Tableau support, it looks like they are going to commission that table. I got the roles from REST API, but you can get the Publishers from postgreSQL by joining to the site_roles with users on id=Site Role Id
In the list you get the Server Admins will also be included in Publishers roles, so I added SUM(Lock Version)=1 to get rid of them.