Analysis Services Add role members programmatically in C#

First, I don’t know why; but it seems like as soon as you add in the word OLAP or Analysis Services to your problem things just stop working as you would normally expect.

Problem:

  • I’m using NAnt as a deployment tool for Database versioning and MS OLAP deployment in multiple environments.
  • My service accounts aren’t standardized across environments (don’t get me started with THAT one…)
  • I want to apply different accounts, and ONLY the accounts for those environments to my cube roles.

The “should work out of the box solution”:


Server server = new Server();
server.Connect("mah-olap-server");
Database db = server.Databases.FindByName("MahOLAPDB");
Role role = db.Roles.GetByName("Role");

RoleMember r= new RoleMember("Domain\Account");
role.Members.Add(r);
role.Update();

What’s the big deal right? Well, there are a few problems:

  • Adding it this way un-like through the GUI does not map the user account to an SID
  • Even after you’ve mapped it to an SID it will still add a duplicate role member.

First:

Server server = new Server();
server.Connect("mah-olap-server");
Database db = server.Databases.FindByName("MahOLAPDB");
Role role = db.Roles.GetByName("Role");
RoleMember r= new RoleMember("Domain\Account");
if(!role.Members.Contains(r)){
    role.Members.Add(r);
    role.Update();
}

That is hopeless, I thought at first because of the SID thing – but NO… After the next solution, Role.Members.Contains() does not work very well even though in debugger I have the EXACT same role member I’m adding, and exists in the role. Not only was it not finding it, but it was still adding a duplicate. This along with the not mapping to proper SID – I deemed to be a future-issue I should avoid now. So…

List members = new List(); // I fill this with all the members I need to add
Server server = new Server();
server.Connect("mah-olap-server");
Database db = server.Databases.FindByName("MahOLAPDB");
Role role = db.Roles.GetByName("Role");
//oh wtf, I can't seem to Linq to the role.Members so I'll do it this way, plus case insensitivity on .Remove() doesn't exist
foreach(RoleMember rm in role.Members){
   string er = (from m in members
   where m.Equals(rm.Name, StringComparison.InvariantCultureIgnoreCase)
   select m).FirstOrDefault();

   if(er!=null){
       members.Remove(er);
    }
}

foreach(string m in members){
    //you'll want to wrap in try catch in case the account doesn't exist, you'll get a IdentityNotMappedException
    NTAccount acct = new NTAccount(m);
    //conver it to SID - check if its even real
    SecurityIdentifier sid =   (SecurityIdentifier)acct.Translate(typeof(SecurityIdentifier));
    //convert it back from SID  - this will get us proper account Name format/casing
    acct = (NTAccount)sid.Translate(typeof(NTAccount));
    //create my wholesome role
    //already done my "contains" magic, add that monkey
    role.Members.Add(r);
    role.Update();
    }
}

That final solution actually works out pretty well, and accomplishes a lot of items I would have skipped had the first one just “worked”:

  • Does the mapping of user accounts prior to adding to the role.  Make sure name format, and SID is added.
  • Creates the same behavior as when you add it through VS or SSMS GUI (if you add one that exists it does nothing)
  • Wasted countless hours
  • No over-engineered minute step of my deployment

And it did solve several problems along the way:

  • Creating RoleMembers with just the name does not map it to AD or a proper SID
  • Using the Role.Members.Contains() doesn’t seem to work well
  • You can’t make .Remove() on a List<string> case insensitive

I will try to write a follow up soon regarding how to determine what environment (primary domain your machine is connecting to) you’re in from NAnt

3 thoughts on “Analysis Services Add role members programmatically in C#

  1. I went through the exact same pain as you.
    Found your post and resolved our user add problems in minutes.
    Thanks for posting.

    Here’s what I implemented,

    NTAccount acct = new NTAccount(usr);
    SecurityIdentifier sid = (SecurityIdentifier)acct.Translate(typeof(SecurityIdentifier));
    rl.Members.Add(new RoleMember(usr, sid.ToString()));
    rl.Update();

  2. I think your solution is ok but I believe right now you should think about how to update the SSAS BI solution with the users added via c#, in the BI solution there’s a file called {rolename}.role which is basically a XML file containing the cube’s roles so if you add a user via c# this file doesn’t get affected and if someone deploys the cube using this BI solution all the users you added via c# will be lost, I was thinking about Reading this XML using linq to XML and add the element algo in this file. if you can come up with a better solution please reply. thanks in advance.

    1. Agreed Gilberto, in my case this was part of a deployment tool – so the accounts being added would change based on the project/client the new cube template was being deployed for.

      In this case the SSAS BI solution only maintained the structure of the cube, and my deployments would manage relevant roles and permissions. In no instances would the cube be deployed directly from the solution – other than in a development environment.

      Certainly in a custom-case, you’d want a way to sync back this information to your solution. Updating the XML like you suggested should work, you just have to be careful to match the syntax which VS reads/writes to the metadata files.

Leave a Reply

Your email address will not be published. Required fields are marked *