SSAS Edit Cube Roles Without Interfering with SSAS Studio

• Download source - 594.3 KB

What Problem Does This Solution Solve?

Manipulating Users security in Role model of Microsoft SSAS Cube.

How Does This Help Someone?

Using just the solution, you will be able to add/remove users to defined Roles in SSAS Cube, and by using all library functions you can manipulate the SSAS Cube entirely except cube design.

Background

You should be aware of C#, and SSAS roles.

How Does the Code Actually Work?

You should reassign the variables of LDAP path, and change the server name, and Cube name in the app.config to make it work. then build and run.

What is Going Inside the Code Snippets?

I had 3 main problems to manage this roles task.

But before that, you need to add these DLLs into your solution. 

• Microsoft.AnalysisServices - for dealing with Cubes
• System.DirectoryServices.AccountManagement - for dealing with Active Directory

These DLLs will help a lot for dealing with cube structure - you can do a lot than in this article - and also dealing with Active.

1. Connecting to the Cube Server (aka: SSAS Server)

using Microsoft.AnalysisServices; 
Server server = new Server();
server.Connect(ConfigVals.ServerName);
Database db = server.Databases.FindByName(ConfigVals.CubeName);
 
 if (db == null) throw new Exception("Failed To Connect!, <br />   please check that you have access to the server and cube."); 

Now you are connected to a specific cube from the server.

Note: You can get the list of all available cubes relative to your security from the server and check what you can do with this object on your own.

2. Checking User Names from Active Directory

This part should be easy to search, but take care of the LDAP directory name:

string ldapAddress = "LDAP://<<SERVER>>";
           DirectoryEntry de = new DirectoryEntry(ldapAddress);
           DirectorySearcher ds = new DirectorySearcher(de);

           ds.Filter = "(samaccountname=" + memberName + ")";

           SearchResult rs = ds.FindOne();

Make sure that member does not contain "\" sign. as it searches only for the login name. If you have multiple domains don't be afraid, it takes care of it.

3. Manipulating Role Security

For this task, I had 2 goals:

• Adding New User
• Removing existing User

3.1 Adding New Users

Role role = GetRole(roleName);

              string member = string.Format("{0}\\{1}", DomainName, memberName);

              if (!IsMemberInRole(role, member))
              {
                  role.Members.Add(new RoleMember(member));

                  role.Update();
                  role.Refresh();
              }
              else
                  throw new Exception("User already exists!");

GetRoles is a helper function to get a role by name, so it is no big deal.

IsMemberInRole should take attention as you will find in Role class a property called Members and this is a collection. So theoretically speaking, you can search in it by contains function, but the problem is that it is searching by 2 properties of the member (Name, and SID) and SID is a guid. So, I create a helper function to iterate through members collection and return back the matching member.

Role role = GetRole(roleName);
 bool IsMemberInRole(Role role, string member)
     {
         if (role == null || string.IsNullOrEmpty(member) ||
         string.IsNullOrWhiteSpace(member)) return false;

         foreach (RoleMember item in role.Members)
         {
             if (item.Name.ToLower() == member.ToLower())
                 return true;
         }

         return false;
     }

Finally, you need to call Role.Update() to reflect the changes into server and then call Refresh to reflect what is on server to the class properties.  

3.2 Removing Users

Role role = GetRole(roleName);
                role.Members.RemoveAt(GetMemberIndexInRole(role, memberName));
 
                role.Update();
                role.Refresh(); 

The same concept for removing a user.

Points of Interest

There are some tricks you should take care of while executing the code.

1. Change the target framework from ".NET Framework 4 Client Profile" to ".NET Framework 4 Client Profile"
2. Microsoft.AnalysisServices is not listed by default, you should get it from C:\Program Files\Microsoft.NET\ADOMD.NET\100.