Granting Custom Access to Cell Data

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.

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

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

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

Tasks

Concepts