3

I'm trying to get all the roles from Analysis Services and their dimensions permissions.

I have already been successful, managing to extract users and their roles, however I have not found anywhere if it is possible to extract permissions by dimensions.

I need this listing, because we are creating a permissions dimension from SSAS, in a data warehouse.

The script below on the second picture, I got from here: Extract SSAS Roles and user, but I already tried from "Lucas Notes" aswell: List SSAS User Roles Using PowerShell and PowerShell Script: List SQL Server Analysis Service Role Permission By Database and Cube

I really don't understand anything about PowerShell script, so here's what I've tried:

What I'd like to list aswell (permission to a certain data, or column, or value from a dimension):

Dimension permission

What I've already managed to list:

PowerShell script and result

Guilherme Matheus
  • 573
  • 10
  • 30
  • How about using DMVs https://learn.microsoft.com/en-us/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services?view=asallproducts-allversions, something like TMSCHEMA_TABLE_PERMISSIONS seems promising – Gigga May 28 '20 at 17:34
  • @Gigga If I run `SELECT * FROM $SYSTEM.TMSCHEMA_TABLE_PERMISSIONS;` in a MDX query it returns me nothing, not even the columns. If I run `SELECT * FROM OPENROWSET('MSOLAP','DATASOURCE=FCESPSQLD007; Initial Catalog=OLAP_Oracle;', 'SELECT * FROM $SYSTEM.TMSCHEMA_TABLE_PERMISSIONS');` in a SQL query, it returns no rows, but with the columns. If I run any DMV with `MDSCHEMA` in a MDX query, it works great. So I think I don't know how to do it. – Guilherme Matheus May 28 '20 at 18:14
  • @guilherme_matheus, didn't look close enough to notice you are using multidimensional cube. Here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/92044534-afb6-4c6c-a8ba-7d91d87614fd/need-to-extract-ssas-role-permissions-for-a-given-cube-role?forum=sqlanalysisservices it says you could extend your powershell to get necessary information using the AMO objects as you have already done. There should be a CubePermissions -object under Database.Cube, here's the objectmodel: https://docs.microsoft.com/en-us/analysis-services/amo/amo-concepts-and-object-model?view=asallproducts-allversions – Gigga Jun 01 '20 at 17:50
  • @Gigga I think you gave me a path, I already did some scripts on PowerShell to get PowerBI and Sharepoint data, but on SSAS I think it's to hard for that and there is not much content on web aswell. Can you help me please? Do you know how to make this script? – Guilherme Matheus Jun 09 '20 at 20:15

0 Answers0