none
SSAS - check if the user has a role assigned RRS feed

  • Question

  • 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 (ideally even existing members in the roles) by sending XMLA script to SSAS server?

    Friday, November 22, 2019 12:05 PM

Answers

  • The only request that returns role and member information is DISCOVER_XML_METADATA, but that returns a large XML document with the entire cube structure and design, so it could be tricky to parse in T-SQL. 

    So the other options would be to either keep track of who you've added to roles in a SQL table. Or the other option I normally use is to no add individual users to roles, but instead we add one or two AD groups to a role. Then granting access to the cube is simply a matter of adding or removing people from the group.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Alex_404 Monday, November 25, 2019 9:25 AM
    Sunday, November 24, 2019 9:42 PM
    Moderator

All replies

  • The only request that returns role and member information is DISCOVER_XML_METADATA, but that returns a large XML document with the entire cube structure and design, so it could be tricky to parse in T-SQL. 

    So the other options would be to either keep track of who you've added to roles in a SQL table. Or the other option I normally use is to no add individual users to roles, but instead we add one or two AD groups to a role. Then granting access to the cube is simply a matter of adding or removing people from the group.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Alex_404 Monday, November 25, 2019 9:25 AM
    Sunday, November 24, 2019 9:42 PM
    Moderator
  • I'm trying to solve a problem where the same user might have more than one role (often through being in different AD groups). When roles have security on different dimensions the overlap of them allows the user to see way more than expected.

    So far I've found several discussions where the outcome is always "try individual access". I'm trying to see if individual access would work in my case.

    Thanks, I'll try to work with DISCOVER_XML_METADATA



    • Edited by Alex_404 Monday, November 25, 2019 9:31 AM
    Monday, November 25, 2019 9:27 AM
  • So far I've found several discussions where the outcome is always "try individual access". I'm trying to see if individual access would work in my case.

    So if you add a single person to two roles you will see the same issue. Security roles are always unioned together not intersected. The only way to make security on multiple dimensions work is that you need to create a role for each intersection that you need.

    So instead of creating 2 roles for Country=USA and ProductCategory=Bikes you would create 1 role that has both conditions. Unfortunately this means you could end up with an explosion of roles, but it is the only way to secure across different dimensions.


    http://darren.gosbell.com - please mark correct answers

    Monday, November 25, 2019 9:38 PM
    Moderator
  • Exactly. That's why I need to find a way to check existing access for the person (which DISCOVER_XML_METADATA does)

    Looks like I will have to iterate through all the user's AD groups, trying to find them in the cube. If some access found through the AD group then either the person has access already or manual handling is required.


    • Edited by Alex_404 Tuesday, November 26, 2019 11:59 AM
    Tuesday, November 26, 2019 11:58 AM