Search code examples
sqlsql-serverssasbusiness-intelligencessas-tabular

SSAS : How to generate a csv of all the users having access to a cube?

I need to compare the users listed in a file with the users having access to a certain cube to check which to add and which to remove so it checks out. Problem is, there are about 1000 users, so I'm not gonna be able to do it one by one. Is there a quick way to generate a CSV of all the users of the cube so I can work some Python magic and get the overlap / difference easily ?

Solution
    • Connect to the Cube Server via SSMS.
    • Expand your cube
    • Expand "Roles"
    • Right Click on "ReadOnly" >> "Script Role as" >> "CREATE TO" >> "New Query Editor Window" or "File..."

    enter image description here

    • Now you have a XML-File containing all the Users with access to your cube (One comment: if you/ your server admin are working with security groups and you don't have the rights to look into those security groups, then you need to reach out to him, so that he/she can give you the list of members of this security group).

    If you are not so much into querying XML-Files, here is a pretty simple workaround of how to get your list: - Go to your favourite Editor (Notepad++, etc.) and remove everything except the lines with < Name >.

    • In Notepad++ that can easily be done by highlighting lines with "< Name >" (Press Search STRG+F, then go to highlight and activate "Set Bookmark")
    • then go to "Search" >> "Bookmark" >> "Remove all lines without Bookmarks"
    • Finally Search and Replace "< Name >" and "< / Name >" with "".
    • Now you have your list without annoying XML-Content, which you can for example paste into Excel and compare it via a vlookup with your list or better: you insert both list in sql tables and compare them via sql