How to Audit SSAS role permissions

A while back I was very disappointed to discover that SSAS does not have any easy way to go through all of your dimensions and cubes to see which users have access to which dimensions, cubes, etc.  Sure you can go one by one in order to get the information, but I was really just looking for an easy way to view the permissions based on individual object permissions, similar to the way you can in SSMS.  I called Microsoft and unbelievably they had no solution.  What a pinch to be in when you have an important audit coming and now it is going to take an absorbant amount of time to come up with the data the auditors are going to need.

Well, fortunately for me, I ran into an incredible site today.  It’s called ASSP – Analysis Services Stored Procedure Project This site is great!  It’s an open source project and it is just what the doctor ordered.  You can download the software and open up the project in Visual Studio.  From there you just build the ASSP assembly and you are all set.  This assembly has a number of various functions built into it and it returns a number of helpful datasets in the datasets you will be looking for.  They have also have additional reports and things pre-made and built right in to the project for you as well, so you can deploy those right to SSRS.  Here is an exerpt of one of the funtion calls and documentation:

DiscoverXmlMetaData(path, restrictions)
This function executes a DISCOVER_XML_METADATA command, which returns a hierarchical resultset and then “flattens” that resultset and returns it as a datatable which can be displayed by SSMS or used in a Reporting Services report.

Unfortunately standard Xpath style queries did not prove rich enough to return data in it’s full context. That is you could return a list of all the dimensions in every cube on a given server, but you could not return a column containing the cube and database to which they belonged. So a hybrid Xpath syntax was developed which allows you to specify a list of fields to return at each level.

The easiest way to explain this is with a few examples

In order to return return a list of all the dimensions in every cube in the current database you would issue the following command.

CALL ASSP.discoverXmlMetaData("DatabaseCubesCubeDimensionsDimension");

But this returns the list without any context, If you want to report the CubeName and LastProcessed date you would put a vertical bar (|) after the Cube element in the path and then put a comma separated list of fields that you want returned

CALL ASSP.discoverXmlMetaData("DatabaseCubesCube|Name,LastProcessedDimensionsDimension");

Note: it is not entirely true that the DiscoverXmlMetadata() command returns data without context – one of the features of this function is that it automatically returns the “ID” field of any ancestor elements in the resultset.

If you want to see what sort of paths are available the easiest way is to run the following command in an XMLA query window. You basically need to skip the xsd:schema> element and look at what is returned under the <row><xars:METADATA> elements.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_XML_METADATA</RequestType>
  <Restrictions />
  <Properties />
</Discover>

The path parameter actually matches from the leaf level up, so you could do a command like

CALL ASSP.discoverXmlMetaData("Dimension");

But this would produce a bit of a mess as it would return both cube dimensions and database dimensions and other dimension references from all over the xml resultset. The function is built in such a way that it build the columns for the resultset based off the first populated node in that matches the path so you will end up with a lot of blank columns.

But what this does mean is that you can specify just the minimal path that will unquiely identify the elements you are interested in.

eg

CALL ASSP.discoverXmlMetaData("CubeDimensionsDimension");

returns the same result as

CALL ASSP.discoverXmlMetaData("DatabaseCubesCubeDimensionsDimension");

It should be noted that because this is base on Xml, the path is case sensitive and “dimension” is not the same as “Dimension”.

Role information is not exposed by any of the other schema rowsets. The following commands will extract information about roles, dimension permissions, attribute permissions and cube permissions and would allow for the complete documentation of the security settings.

CALL ASSP.DiscoverXmlMetadata("DatabaseRolesRole");

CALL ASSP.DiscoverXmlMetadata("DatabaseDimensionsDimensionDimensionPermissionsDimensionPermission");

CALL ASSP.DiscoverXmlMetadata("DatabaseDimensionsDimensionDimensionPermissionsDimensionPermissionAttributePermissionsAttributePermission")

CALL ASSP.DiscoverXmlMetadata("DatabaseCubesCubeCubePermissionsCubePermission");

I am so thrilled!  This is just what I was looking for.  Check it out and see for yourselves just how great this assembly is.