Analysis Services Security - SQL Server Analysis Services 2012 Cube Development - page 259

Implementing cell-level security
Restricting access to specific dimension members is normally sufficient even for very
granular security schemes. However, in rare cases, you might have a need to secure
individual cells in addition to specifying allowed and denied dimension member sets.
Although I discuss cell-level security here, be warned that securing at the cell level
can cause severe performance issues, particularly, for large cube implementations.
You can grant read-only, read-write, and read-contingent permissions at cell level.
Read-write permissions only apply if the cube write-back is enabled. Read-contingent
permissions define SSAS behavior for cells that are defined from other cells that are
known as calculated members. If you grant read-contingent permission on a calcu-
lated member, you must ensure that the same role also has access to the members
included in the calculated member's definition.
In addition to imposing performance overhead, cell-level security also introduces an
issue with cube usability. Any cell that the role member isn't allowed to see will still be
visible but will display the #N/A value instead of the actual cell value. You have some
flexibility as far as overwriting #N/A in the client application is concerned. Neverthe-
less, the users will know that the cell exists, but they're not permitted to see its value.
A cleaner solution would have hidden the cell completely so that the user would not
be aware that the secured cell exists.
How to do it...
To restrict a role to see only the [internet order count] measure, perform the
following steps:
1. Create a test role within the Adventure Works DW2012 sample database
using SSMS and grant membership to a valid domain account.
2. Grant the Read access to the Adventure Works cube as discussed earlier
in this chapter.
3. Navigate to the Cell Data tab of the role editor, check the Enable read per-
missions box, and enter the following MDX expression:
Measures.CurrentMember IS
measures.[internet order count]