February 9, 2011 at 1:32 pm
Hi. Does anyone have an MDX query or any other way to list all roles and users for a given SSAS cube database? I've seen similar scripts for regular SQL databases but I can't seem to find any way to do this in SSAS.
We have many roles with many users. Opening up each role and noting all of the users listed would be extremely time consuming. In addition, the user names are keys, like "pmfke02", so it makes it even harder to "see" who is in the role. I would like to be able to get a list of Roles and Users, put it in a temp table or something, then join to my other list that would have that key and the actual users's name. Even if I can just get it on the screen in a way that I can copy/paste it into Excel or some way I can work with it.
Anyone have any help for this? Thanks.
- Cindy
February 13, 2011 at 1:07 pm
Hi Cindy,
Unfortunately, no there is no mdx you can use from the out-of-the-box SSAS to get a list of users and their roles.
However there is an SSAS codeplex project that is able obtain XMLA info. This is a .NET assembly that transforms various SSAS meta-data XMLA discover commands into a rowset. It can be used in a MDX command (call)
Where to find it:
http://asstoredprocedures.codeplex.com/wikipage?title=XmlaDiscover&version=12
To obtain a list of roles, basic examples are given in the codeproject. I am using this assembly to obtain the same information into a SSRS report.
If you have any questions about installing and using this command feel free to ask
Regards Kees
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply