Garrett Patterson

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