What is the best way to obtain the complete list of permission of a given user using AMO in C#

Question

I am using Visual Studio 2013(C#) and SSAS 2014 (through AMO). I need to prepare a list of permissions of a given user in the SSAS database. For example, domainName\userName has permissions on the 2 dimensions out of 5 available in the database. I like to prepare a list like this.

Dimension Name | Attributes | Dimension used in Cube | VisualTotal | Mdx Set (if any) | Role Name

I can loop through Roles and members and get some information. But it seems that it is a long shot and will not be performance friendly in the production environment.

score 1 · Answer 1 · answered Apr 16 '16 at 00:27

1

Try checking out Analysis Services Stored Procedure Project and BIDS Helper. I have not tried any of these, however, first claims

"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."

and the second:

"To summarize all the security permissions granted via the roles instead of having to click into dozens of screens to find this information."

answered Apr 16 '16 at 00:27

Janis S.

2,526
22
32

Thank you Janis. I started looking into it. It seems this can not be achieved using AMO through VS 2013. right? – Shivalik Chakravarty Apr 16 '16 at 01:25
I think it should be possible because AMO is exposing SSAS objects, however, could not come up with an example about retrieving permissions in multidimensional model. Came across only the one which is regards tabular model: http://stackoverflow.com/questions/29646645/c-sharp-amo-get-roles-complete. Maybe this could serve as an example. – Janis S. Apr 16 '16 at 01:47

score 1 · Accepted Answer · answered Apr 20 '16 at 09:19

Finally I am able to achieve this using AMO. Here is what I did to solve this. Following is the code snipped to find out the list of permissions one user has in a given SSAS database. This result is then loaded into a hypothetical DataTable. I hope this will help others trying to find out a similar solution.

using (Server Srv = new Server())
{   
    Srv.Connect("\\serverName\instanceName");                       
    Database d = Srv.Databases.FindByName("My SSAS DB");    
    foreach (Role r in d.Roles)
    {
        foreach (RoleMember m in r.Members)
        {
            if (string.Compare(m.Name, "domainName\userName", StringComparison.InvariantCultureIgnoreCase) == 0)
            {               
                foreach (Cube c in d.Cubes)
                {                                        
                    CubePermission cp = c.CubePermissions.FindByRole(r.ID);
                    if(!(cp == null))
                    {   
                        foreach(CubeDimensionPermission cdimPerm in cp.DimensionPermissions)
                        {
                            foreach(AttributePermission attPerm in cdimPerm.AttributePermissions)
                            {                               
                                DataRow dr = dt.NewRow();
                                dr["Database Name"] = d.Name;
                                dr["Role Name"] = r.Name;
                                dr["Dimension Name"] = cdimPerm.CubeDimension.Name;
                                dr["Cube Name"] = c.Name;
                                dr["Attribute Name"] = attPerm.Attribute.Name;
                                dr["AllowedSet"] = attPerm.AllowedSet;
                                dr["DeniedSet"] = attPerm.DeniedSet;
                                dr["DefaultMember"] = attPerm.DefaultMember;
                                dr["VisualTotals"] = attPerm.VisualTotals;
                                dt.Rows.Add(dr);
                            }
                        }                                            
                    }
                }                                    
            }
        }
    }                   
    Srv.Disconnect();


}

What is the best way to obtain the complete list of permission of a given user using AMO in C#

2 Answers2

Linked