Granting Custom Access to Cell Data
After you grant a database role in Microsoft SQL Server 2005 Analysis Services (SSAS) read or read/write permissions to a cube, role members have access to view all cell data. To limit access to specific cells, you must specifically restrict cell access. To restrict access to specific dimension members, see Granting Custom Access to Cell Data.
To grant a database role access to specific cells, you use a Multidimensional Expressions (MDX) expression to define a range of cells with read, read contingent, or read/write permissions. For more information, see Using MDX Expressions to Set Cell Data Permissions.
Guidelines for Setting Permissions
Regardless of whether a database role has read, read contingent, or read/write permissions to cell data, there are some guidelines and background information that can make dealing with these permissions easier.
Take care when granting permissions to derived cells
A derived cell obtains its data from other cells. If the database role has permission to the derived cell, but not to the cells from which the derived cell obtains its values, it may be possible for a member of that database role to infer the values of cells to which he or she does not have permission. For example, a database role has permission to the cells for the Sales and Profit measures (that is, these measures are visible to the database role), but the role does not have permission to the cells for the Cost measure. A member of this database role can determine values for the Cost measure by subtracting the Profit measure values from the Sales measure values.Cell data permissions cannot be higher than permissions on the cube
Permissions that are granted on specific cells cannot exceed the permissions that are granted to a database role on the entire cube. For example, a database role has read/write permission on a cell, but that same database role only has read permission on the cube. The cell data permission will not be read/write; it will be read.Access to cell data does not give access to dimension data
Although a database role may have access to cell data, that role will not have access to dimension data unless the role has been given read or read/write permissions to the dimension data separately. Access to cube data can restrict access to dimension attributes to which the database role has access, but cannot extend access to dimension attributes to which the database role does not have access.Denying access to cell data does not hide the cell from a user
The access permissions for cell data do not control whether a database role is able to see a cell, but rather whether the role is able to see the contents of the cell. If a database role does not have access to cell data, the cell is still visible in the results of a query. However, the cell contains the value of #N/A instead of the actual cell value. The #N/A value appears in the cell unless the client application translates the value, or another value is specified by setting the Secured Cell Value property in the connection string.If you do not want the cell to be visible in the results, you have to limit the members—dimensions, dimension attributes, and dimension attribute members—that are viewable. For more information, see Granting Dimension Access and Granting Custom Access to Dimension Data.
Setting Read Permission to Cell Data
Cells to which a database role has read permission are viewable by members of the database role. These cells are viewable even if these cells are derived from cells to which the database role does not have any access permissions.
For example, suppose there is a calculated measure called Profit that is derived from cells for the Sales and Cost measures. If a database role has read permission on the Profit cells, this measure is viewable even if that database role does not have permission to the cells for the Sales or Cost measures.
Note
If a database role is granted read permission on a subset of cube cells, but that role does not have an MDX expression specifying the cells to which read permissions are granted, the database role does not have read permission on any cells in the cube. This is because the default allowed set is an empty set when Analysis Services resolves a subset of cube cells.
To grant access permissions to a member in a dimension, a user must be a member of the Analysis Services server role or a member of an Analysis Services database role that has Full Control (Administrator) permissions.
To give a database role read access to cell data
In SQL Server Management Studio, connect to the instance of Analysis Services, expand Roles for the appropriate database in Object Explorer, and then click a database role (or create a new database role).
Click Cube Cell Data in the Select a Page pane, select the cube in the Cube list, and then select the Enable Read Permissions check box.
If no members are entered in the Allow Reading of Cube Content box, all cube cells are viewable.
To specifically grant access only to certain members, enter the MDX expression for those cube cells in the Allow Reading of Cube Content box. No other cube cells will be viewable.
To help build the MDX expression, next to Edit MDX, click the browse button, and then use the MDX Builder window that appears to build your MDX expression.
For examples of MDX expressions used to set cell data permissions, see Using MDX Expressions to Set Cell Data Permissions.
Setting Read Contingent Permission to Cell Data
Cells to which a database role has read contingent permission are only viewable if one of the following conditions is true:
The cells with read contingent permission are not derived from other cells.
The cells with read contingent permission are derived from other cells, but the database role has read permission on all the cells from which the cell was derived.
For example, a database role has read contingent permission on Profit cells. The Profit cells are derived from the Sales and Cost measures. In this case, the Profit cells are viewable only if the database role has read permission for both the Sales and Cost measures. If the database role had read contingent permission to Cost cells, and the Cost measure was derived from other cells, the Profit cells would be viewable only if the database role had read permission to the cells from which the Cost measure was derived. Thus, with the read contingent permission, there can be a chain of contingencies when a cell is derived from others, which in turn are derived from others, and so on.
Note
If a database role has both the read and read contingent permissions on a cell, the role has read permission on the cell.
Note
If a database role is granted read contingent permission on a subset of cube cells, but that role does not have an MDX expression specifying the cells to which read contingent permissions are granted, the database role does not have read contingent permission on any cells in the cube. This is because the default allowed set is an empty set when Analysis Services resolves a subset of cube cells.
To grant access permissions to a member in a dimension, a user must be a member of the Analysis Services server role or a member of an Analysis Services database role that has Full Control (Administrator) permissions.
To give a database role read contingent access to cell data
In SQL Server Management Studio, connect to the instance of Analysis Services, , expand Roles for the appropriate database in Object Explorer, and then click a database role (or create a new database role).
Click Cube Cell Data in the Select a Page pane, select the cube in the Cube list, and then select the Enable ReadContingent Permissions check box.
In the Allow reading of cell content contingent on cell security box, enter an MDX expression that identifies the cells to which the database role has read contingent permissions.
To help build the MDX expression, next to Edit MDX, click the browse button, and then use the MDX Builder window that appears to build your MDX expression.
For examples of MDX expressions used to set cell data permissions, see Using MDX Expressions to Set Cell Data Permissions.
Setting Read/Write Permission to Cell Data
Cells to which a database role has read/write permissions are viewable and updatable by members of the database role, provided that members have read/write permissions to the cube itself. Permissions that are granted at the cell level cannot be greater than the permissions that are granted at the cube level.
Note
If a database role is granted read/write permissions on a subset of cube cells, but that role does not have an MDX expression specifying the cells to which read/write permissions are granted, the database role does not have read/write permissions on any cells in the cube. This is because the default allowed set is an empty set when Analysis Services resolves a subset of cube cells.
To grant or deny access permissions to specific cells, a user must be a member of the Analysis Services server role or a member of an Analysis Services database role that has Full Control (Administrator) permissions.
To give a database role read/write access to cell data
In SQL Server Management Studio, connect to the instance of Analysis Services, expand Roles for the appropriate database in Object Explorer, and then click a database role (or create a new database role).
Click Cube Cell Data in the Select a Page pane, select the cube in the Cube list, and then select the Enable Read/Write Permissions check box.
In the Allow reading and writing of cube content box, enter an MDX expression that identifies the cells to which the database role has read/write permissions.
To help build the MDX expression, next to Edit MDX, click the browse button, and then use the MDX Builder window that appears to build your MDX expression.
For examples of MDX expressions that are used to set cell data permissions, see Using MDX Expressions to Set Cell Data Permissions.