The Multidimensional Expressions (MDX) expression that sets permissions to cell data can either specify which cells can be accessed or which cells cannot be accessed. By allowing access to cells for specific members in some dimensions and denying access to cells for specific members in other dimensions, cell security provides great flexibility. In fact, you can create an MDX expression that allows or denies access to any possible combination of cells.

When you create the MDX expression, remember the following:

• The MDX expression does not need to include every dimension in the cube. If you omit a dimension in the MDX expression, access to dimension members is not affected.
  
• To restrict access by only one dimension, a relatively simple MDX expression usually suffices.
  
• The complexity of the MDX expression depends largely on the number of dimensions it includes.
  
• To allow or deny access to cells for a specific member or measure, use the MDX functions, CurrentMember and Name, in combination.
  
• If you specify a member name that is not unique within the dimension, use the UniqueName function instead of the Name function.
  
• To allow or deny access to the cells for a member and its descendants, include the Ancestor function in the expression.
  
• To allow or deny access to cells based on criteria in multiple dimensions, create an expression for each dimension, and then combine them with AND or OR into one expression.
  

 Reviewing Examples of MDX Expressions that Set Permissions

The following topics illustrate how various MDX expressions can be used to control access to cell data:

• Example 1—Permitting Access to All Members
  Uses the default expression to return all members.
  
• Example 2—Permitting Access to a Single Member
  Uses the CurrentMember function to return only a single measure.
  
• Example 3—Denying Access to a Single Member
  Uses the CurrentMember function to prevent access to a single measure.
  
• Example 4—Limiting Access to a Member and its Descendants
  Uses the Ancestor and CurrentMember functions to return only a single member and its descendants.
  
• Example 5—Giving Access to a Specific Measure Within a Dimension
  Uses And to combine two expressions that contain the Ancestor and CurrentMember functions into a single expression that includes only a specific measure from a dimension.
  
• Example 6—Excluding Selected Measures from a Dimension
  Uses And to combine two expressions that contain the Ancestor and CurrentMember functions into a single expression that excludes selected measures from a dimension.
  
• Example 7—Making Exceptions to Denied Members
  Uses And to combine two expressions that contain the Ancestor and CurrentMember functions into a single expression that excludes a selected measure from every dimension except one particular dimension.
  

 See Also

Concepts

Granting Custom Access to Cell Data

Help and Information

Getting SQL Server 2008 Assistance