SSAS – check if the user has a role assigned

I’m trying to create a way to partially automate creation of roles and role permissions in SSAS multidimensional cube. Due to some restrictions on my side this can only be done with using T-SQL to generate XMLA scripts and sending them to SSAS server.

I have XMLA scripts to create, update and delete roles. However, I do not have a way to check if a certain user already has a role assigned in the cube.

I have found a way to get a response from SSAS server using discover, for example:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>MDSCHEMA_CUBES</RequestType>
    <Restrictions />
    <Properties>
        <PropertyList>
            <Catalog>MyCube</Catalog>
        </PropertyList>
    </Properties>
</Discover>

which returns a list of existing cube in the catalog. I’ve found a list of existing Request types here: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms126079(v=sql.110)

but I see no type which returns existing roles and/or existing users in the role

Any ideas how this can be done? To get a list of existing roles or existing members in the roles by sending XMLA script to SSAS server?

Answer

DISCOVER_XML_METADATA can be used for this purpose, it returns the whole SSAS database structure so some parsing is needed to get the roles and role members specifically.

Advertisement

Answer