Analysis Management Objects (AMO) opens up the object model of SSAS cube for programming. With a bit of .NET programing, AMO can be effectively used to automate administration tasks. Securing the cube is one such task that can be easily automated using AMO. The rest of this post explains how to automate cube security using AMO.

SSAS security is role-based where object permissions are defined at role level. Members/users are then granted membership to roles. Role members are either windows users or windows groups. Object permissions can be defined on

• Analysis Service Database
• Cubes
• Shared Database Dimensions
• Cube Dimensions and Role Playing Dimensions
• Cells (Cell Data Access)
• Dimension Attribute Members (Dimension Data Access) and
• Data Mining Models

The sequence of steps to secure SSAS cube using AMO would be:

1. Instantiate Major Objects of Analysis Services which is Analysis Server, Analysis Service Database and Cube
2. Backup Database before securing cube (recommended)
3. Create Role.
4. Add Member to role.
5. Grant Read Access on database to role.
6. Grant Read Definition on database to role (optional)
7. Grant Read Access on cube to role.
8. Grant Read Access to shared database Dimension and/or Cube Dimension.
9. Grant Read Access to dimension attribute

To automate these steps a metadata table would be required that stores the following information which would be passed as parameters to the AMO routine

• Role Name
• Role Member (Windows user/windows Group)
• ID of Database to be secured
• ID of Cube to be secured.
• ID of Dimension that is to be secured
• ID of Dimension Attribute that is to be secured.
• Flag to indicate Shared Database Dimension or Cube Dimension
• Columns required create the Attribute Member MDX expression that will be used to secure the Attribute Member.

Code Snippet

A simple implementation of using wrappers over AMO to automate cube security would be as shown below. A detailed explanation of individual methods is given in subsequent sections

//Instantiate Major Objects
CsaWrappers csa = new CsaWrappers(server, database, cube);
//Backup Database (recommended)
csa. BackupDatabase(filename);
//for each metadata record
while (metadata.Read())
{
//Create Role and add member to role
csa.AddMemberToRole(roleMember, roleName);
//Grant Database Read Access
csa.GrantDatabaseRead(roleName);
//Grant Cube Read Access to role
csa.GrantCubeRead(roleName);
//Grant Cube Dimension Access to role
csa.GrantDimensionRead(roleName, dimName);
//Grant Cube Dimension Access to role
csa.GrantDimensionDataRead(roleName, dimName, dimAttribName, mdxExp);
}

Pre-requisites

1. To program AMO, download and install Microsoft Analysis Management Objects (MSAMO) installer from the relevant version of SQL Server Feature Pack.
2. Create a Console Application project in Visual Studio and create a reference to Microsoft.AnalysisServices
3. Create a new class file and add the namespace Microsoft.AnalysisServices

Constructor

• The constructor instantiates the Major Analysis Service Objects namely the Analysis Server, Analysis Service Database and Cube using the parameters passed
• It is important to pass the ID of Database and Cube and NOT the names. The ID and Name of the Database and Cube may not always be the same especially if the names are modified after initial build.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException

public CsaWrappers(string server, string database, string cube)
{
 try
{
//Server
this.Server = new Server();
this.Server.Connect(server);
 //Database
this.Database = this.Server.Databases.FindByName(database);
 //Cube
this.Cube = this.Database.Cubes.FindByName(cube);
 }
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: Constructor failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (server == null) { throw new AmoException("ERROR: server parameter supplied with NULL value to constructor"); }
if (database == null) { throw new AmoException("ERROR: database parameter supplied with NULL value to constructor"); }
if (cube == null) { throw new AmoException("ERROR: cube parameter supplied with NULL value to constructor"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: Constructor failed with exception " + OpException.Message + ". Parameters passed were server=" + server + ",database=" + database + ",cube=" + cube);
}
catch (AmoException GenericAmoException)
{
if (this.Database == null)
throw new AmoException("ERROR: Unable to connect to Database '" + database + "' on '" + server + "'");
if (this.Cube == null)
throw new AmoException("ERROR: Unable to connect to Cube '" + cube + "' within '" + database + "' on '" + server + "'");
 throw GenericAmoException;
}
 }

BackupDatabase()

• Backs up Analysis Service Database using the fully qualified filename as parameter.
• Timestamp the filename parameter to create a rolling backup
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int BackupDatabase(string fileName)
{
int retVal = 1;
try
{
this.Database.Backup(this.Database.DbStorageLocation + fileName, true);
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: BackupDatabase() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (fileName == null) { throw new AmoException("ERROR: fileName parameter supplied with NULL value to BackupDatabase()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: BackupDatabase() failed with exception " + OpException.Message + ". Parameters passed were fileName =" + fileName);
}
catch (AmoException GenericAmoException)
{
if (fileName.Trim() == "") { throw new AmoException("ERROR: fileName parameter supplied with blank value to BackupDatabase()"); }
 throw GenericAmoException;
}
 return retVal;
}

AddRole()

• Creates a Role in Database if it does not exist already.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException

public int AddRole(String roleName)
{
int retVal = 1;
try
{
if (this.Database.Roles.FindByName(roleName) == null)
{
Role role = this.Database.Roles.Add(roleName);
role.Update();
};
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: AddRole() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to AddRole()"); }
throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: AddRole() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "")
throw new AmoException("ERROR: roleName parameter supplied with blank value to AddRole()");
throw GenericAmoException;
}
 return retVal;
 }

AddMemberToRole()

• This is the Role Membership method that adds a windows user/group to an existing Role.
• Creates the Role if it does not exist.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException

public int AddMemberToRole(String memberName, String roleName)
{
int retVal = 1;
bool memberExists = false;
 try
{
//Create role if it does not exist
Role role = this.Database.Roles.FindByName(roleName);
if (role == null)
{
role = this.Database.Roles.Add(roleName);
this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
}
 //check role membership already exists for windows user
foreach (RoleMember member in role.Members)
{
if (member.Name.Contains(memberName))
memberExists = true;
}
 //Add member to role if not already a member
if (!memberExists)
{
role.Members.Add(new RoleMember(memberName));
role.Update(UpdateOptions.AlterDependents, UpdateMode.CreateOrReplace);
}
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: AddmemberToRole() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (memberName == null) { throw new AmoException("ERROR: memberName parameter supplied with NULL value to AddMemberToRole()"); }
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to AddMemberToRole()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: AddMemberToRole() failed with exception " + OpException.Message + ". Parameters passed were memberName =" + memberName + ",roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (memberName.Trim() == "") { throw new AmoException("ERROR: memberName parameter supplied with blank value to AddMemberToRole()"); }
if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to AddMemberToRole()"); }
 throw GenericAmoException;
}
 return retVal;
}

GrantDatabaseRead()

• Every role created must be given read permission to the Analysis Service Database. Use this method to grant read access to Analysis Service Database for a role.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException

public int GrantDatabaseRead(String roleName)
{
int retVal = 1;
 try
{
Role role = this.Database.Roles.FindByName(roleName);
if (role == null) return -1;
 DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID);
if (dbPermission == null)
{
dbPermission = new DatabasePermission();
dbPermission.RoleID = role.ID;
dbPermission.ID = role.Name;
dbPermission.Name = role.Name;
dbPermission.Read = ReadAccess.Allowed;
this.Database.DatabasePermissions.Add(dbPermission);
}
else
{
dbPermission.Read = ReadAccess.Allowed;
}
 dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
 }
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantCubeRead() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantCubeRead()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantCubeRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantCubeRead()"); }
 throw GenericAmoException;
}
 return retVal;
}

Overloaded GrantDatabaseRead()

• The overloaded GrantDatabaseRead() method grants read access of Analysis Service Database for ALL roles.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int GrantDatabaseRead()
{
int intRetval = 1;
 try
{
foreach (Role role in this.Database.Roles)
{
DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID);
if (dbPermission == null)
{
dbPermission = new DatabasePermission();
dbPermission.RoleID = role.ID;
dbPermission.ID = role.Name;
dbPermission.Name = role.Name;
dbPermission.Read = ReadAccess.Allowed;
this.Database.DatabasePermissions.Add(dbPermission);
dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
}
else
{
dbPermission.Read = ReadAccess.Allowed;
}
}
//update database just once (more efficient this way)
this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
 }
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantDatabaseReadDefinition() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
 catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantDatabaseReadDefinition() failed with exception " + OpException.Message);
}
catch (AmoException GenericAmoException)
{
throw GenericAmoException;
}
 return intRetval;
}

GrantDatabaseReadDefinition()

• Database Read Definition permission is mostly optional and is not required for browsing the SSAS cube. Usually required only to examine Database metadata from SQL Server Management Studio.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int GrantDatabaseReadDefinition(String roleName)
{
int intRetval = 1;
 try
{
Role role = this.Database.Roles.FindByName(roleName);
if (role == null)
return -1;
 DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID);
if (dbPermission == null)
{
dbPermission = new DatabasePermission();
dbPermission.RoleID = role.ID;
dbPermission.ID = role.Name;
dbPermission.Name = role.Name;
dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed;
dbPermission.Read = ReadAccess.Allowed;//grant read access
this.Database.DatabasePermissions.Add(dbPermission);
}
else
{
dbPermission.Read = ReadAccess.Allowed;//grant read access
dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed;
}
dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantDatabaseReadDefinition() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDatabaseReadDefinition()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantDatabaseReadDefinition() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDatabaseReadDefinition()"); }
 throw GenericAmoException;
}
 return intRetval;
}

Overloaded GrantDatabaseReadDefinition()

• The overloaded GrantDatabaseReadDefinition() method grants read definition permission of the Analysis Service Database to ALL roles.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int GrantDatabaseReadDefinition()
{
int intRetval = 1;
 try
{
foreach (Role role in this.Database.Roles)
{
DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID);
if (dbPermission == null)
{
dbPermission = new DatabasePermission();
dbPermission.RoleID = role.ID;
dbPermission.ID = role.Name;
dbPermission.Name = role.Name;
dbPermission.Read = ReadAccess.Allowed;//grant read access
dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed;
this.Database.DatabasePermissions.Add(dbPermission);
dbPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
}
else
{
dbPermission.Read = ReadAccess.Allowed;//grant read access
dbPermission.ReadDefinition = ReadDefinitionAccess.Allowed;
}
}
//update database just once (more efficient this way)
this.Database.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
 }
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantDatabaseReadDefinition() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantDatabaseReadDefinition() failed with exception " + OpException.Message);
}
catch (AmoException GenericAmoException)
{
throw GenericAmoException;
}
return intRetval;
}

GrantCubeRead()

• This method grants the role read access to cube.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int GrantCubeRead(String roleName)
{
int retVal = 1;
 try
{
Role role = this.Database.Roles.FindByName(roleName);
if (role == null) return -1;
 CubePermission cubeReadPermission = this.Cube.CubePermissions.FindByRole(role.ID);
 if (cubeReadPermission == null) //no permissions
cubeReadPermission = this.Cube.CubePermissions.Add(role.ID);
 cubeReadPermission.Read = ReadAccess.Allowed;
cubeReadPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
 }
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantCubeRead() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantCubeRead()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantCubeRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantCubeRead()"); }
 throw GenericAmoException;
}
 return retVal;
}

GrantDimensionRead()

• By default Database Read access implicitly grants read access to Database Dimensions and Cube Read access implicitly grants access to the Cube Dimensions. Use this method only if Dimension Data Access is to be defined on the Dimension’s Attribute.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int GrantDimensionRead(String roleName, String dimensionName)
{
int retVal = 1;
try
{
Role role = this.Database.Roles.FindByName(roleName);
if (role == null) return -1;
 Dimension dimension = this.Database.Dimensions.FindByName(dimensionName);
if (dimension == null) return -1;
 DimensionPermission dimReadPermission = dimension.DimensionPermissions.FindByRole(role.ID);
if (dimReadPermission == null) //no permission
dimReadPermission = dimension.DimensionPermissions.Add(role.ID);
 dimReadPermission.Read = ReadAccess.Allowed;
dimReadPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
 }
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantDimensionRead() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDimensionRead()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantDimensionRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDimensionRead()"); }
 throw GenericAmoException;
}
return retVal;
}

Overloaded GrantDimensionRead()

• Grants read permission on all dimensions in the Analysis Service Database to the role. This is an expensive operation and time duration to complete is directly proportional to the number of dimensions and roles in the Analysis Service Database.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int GrantDimensionRead(String roleName)
{
int retVal = 1;
try
{
Role role = this.Database.Roles.FindByName(roleName);
if (role == null) return -1;
 foreach (Dimension dimension in this.Database.Dimensions)
{
DimensionPermission dimReadPermission = dimension.DimensionPermissions.FindByRole(role.ID);
 if (dimReadPermission == null) //no permission
dimReadPermission = dimension.DimensionPermissions.Add(role.ID);
 dimReadPermission.Read = ReadAccess.Allowed;
dimReadPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
}
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantDimensionRead() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDimensionRead()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantDimensionRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDimensionRead()"); }
throw GenericAmoException;
}
return retVal;
}

GrantDimensionDataRead()

• This method defines the Dimension Data Access by securing the members of the Dimension Attribute using an MDX expression.
• Test the MDX expression before passing here.
• Passing an Invalid MDX Expression will not secure the Dimension Attribute Data and doesn not throw an exception.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

   

public int GrantDimensionDataRead(string roleName, string dimensionName, string attribName, string mdxExpression)
{
int retVal = 1;
try
{
Role role = this.Database.Roles.FindByName(roleName);
if (role == null) return -1;
 Dimension dim = this.Database.Dimensions.FindByName(dimensionName);
if (dim == null) return -1;
 DimensionPermission dimPermission = dim.DimensionPermissions.GetByRole(role.ID);
 if (dimPermission == null)
dimPermission = dim.DimensionPermissions.Add(role.ID);
 AttributePermission dimAttrPermission = dimPermission.AttributePermissions.Find(attribName);
 if (dimAttrPermission == null)
{
dimAttrPermission = new AttributePermission();
 dimAttrPermission.AllowedSet = mdxExpression;
dimAttrPermission.VisualTotals = "1";
DimensionAttribute dimAttrib = dim.Attributes.FindByName(attribName);
if (dimAttrib == null)
return -1;
dimAttrPermission.AttributeID = dimAttrib.ID;
 dimPermission.AttributePermissions.Add(dimAttrPermission);
}
else
{
dimAttrPermission.AllowedSet = mdxExpression;
dimAttrPermission.VisualTotals = "1";
dimAttrPermission.AttributeID = attribName;
}
 dimPermission.Update(UpdateOptions.AlterDependents, UpdateMode.UpdateOrCreate);
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: GrantDimensionDataRead() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to GrantDimensionDataRead()"); }
if (dimensionName == null) { throw new AmoException("ERROR: dimensionName parameter supplied with NULL value to GrantDimensionDataRead()"); }
if (mdxExpression == null) { throw new AmoException("ERROR: mdxExpression parameter supplied with NULL value to GrantDimensionDataRead()"); }
if (attribName == null) { throw new AmoException("ERROR: attribName parameter supplied with NULL value to GrantDimensionDataRead()"); }
 throw ArgNullException;
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: GrantDimensionDataRead() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName + ",dimensionName=" + dimensionName + ",attribName=" + attribName + ",mdxExpression=" + mdxExpression);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "") { throw new AmoException("ERROR: roleName parameter supplied with blank value to GrantDimensionDataRead()"); }
If (dimensionName.Trim() == "") { throw new AmoException("ERROR: dimensionName parameter supplied with blank value to GrantDimensionDataRead()"); }
if (mdxExpression.Trim() == "") { throw new AmoException("ERROR: mdxExpression parameter supplied with blank value to GrantDimensionDataRead()"); }
if (attribName.Trim() == "") { throw new AmoException("ERROR: attribName parameter supplied with blank value to GrantDimensionDataRead()"); }
 throw GenericAmoException;
}
 return retVal;
}

DropRole()

• This method deletes the role from Analysis Service Database. All permissions associated with the role on Cube, Dimension and Dimension Data will also be deleted.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int DropRole(String roleName)
{
int retVal = 1;
Role role = this.Database.Roles.FindByName(roleName);
try
{
if (role == null)
return retVal;
 //delete members from role
if (role.Members.Count > 0)
{
role.Members.Clear();
role.Update();
}
 //delete dimension permissions for the role
foreach (Dimension dim in this.Database.Dimensions)
{
DimensionPermission dimPermission = dim.DimensionPermissions.FindByRole(role.ID);
 if (dimPermission != null)
{
dimPermission.AttributePermissions.Clear();
dimPermission.Drop(DropOptions.AlterOrDeleteDependents);
}
}
//delete cube permissions for the role
CubePermission cubePermission = this.Cube.CubePermissions.FindByRole(role.ID);
if (cubePermission != null) cubePermission.Drop(DropOptions.AlterOrDeleteDependents);
 //delete database permissions for role
DatabasePermission dbPermission = this.Database.DatabasePermissions.FindByRole(role.ID);
if (dbPermission != null) dbPermission.Drop(DropOptions.AlterOrDeleteDependents);
//dbPermission.Update();
 //finally delete role from database
role.Drop(DropOptions.AlterOrDeleteDependents);
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: DropRole() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (ArgumentNullException ArgNullException)
{
if (roleName == null) { throw new AmoException("ERROR: roleName parameter supplied with NULL value to DropRole()"); }
throw ArgNullException;
}
catch (OperationException OpException)
{
role.Refresh(true);
throw new AmoException("ERROR: DropRole() failed with exception " + OpException.Message + ". Parameters passed were roleName=" + roleName);
}
catch (AmoException GenericAmoException)
{
if (roleName.Trim() == "")
throw new AmoException("ERROR: roleName parameter supplied with blank value to DropRole()");
throw GenericAmoException;
}
return retVal;
}

DropAllRoles()

• Deletes ALL roles from the Analysis Service Database. All permissions associated with the role on Cube, Dimension and Dimension Data will also be deleted.
• Use this method with care. This is an expensive operation.
• Throws OutOfMemoryException, ConnectionException, ArgumentNullException, OperationException and AmoException.

public int DropAllRoles()
{
int retVal = 1;
try
{
List<Role> roles = new List<Role>();
foreach (Role role in this.Database.Roles)
{
roles.Add(role);
}
foreach (Role role in roles)
{
this.DropRole(role.Name);
}
}
catch (OutOfMemoryException memoryException)
{
throw new OutOfMemoryException("ERROR: DropAllRoles() failed with out of memory exception. The exception message is " + memoryException.Message);
}
catch (ConnectionException ServerNotFoundException)
{
throw new ConnectionException("ERROR: Unable to connect to Analysis Server '" + this.Server + "'. Connection failed with error message " + ServerNotFoundException.Message);
}
catch (OperationException OpException)
{
throw new AmoException("ERROR: DropAllRoles() failed with exception " + OpException.Message);
}
catch (AmoException GenericAmoException)
{
throw GenericAmoException;
}
 return retVal;
}

There are few important pointers to watch out for when using AMO, which I intend to cover in next post. Meanwhile here are few links related to SSAS cube security.

Related Links:

• AMO Coding Tips
• Cell Data Access Vs. Dimension Data Access
• Securing Role Playing Dimensions
• How to Automate Analysis Services Database Backup?
• Membership in Multiple Roles Could Throw Unexpected Surprises
• Testing/Troubleshooting Analysis Service Cube Security from SQL Server Management Studio

https://twitter.com/cairnz/status/80910303793119233