Securing Dimension Data - Microsoft SQL Server 2008 Analysis Services - page 740

<AllowedSet/>
<DeniedSet>{[Customer].[State Province].&amp;[WA]}</DeniedSet>
<VisualTotals>0</VisualTotals>
</AttributePermission>
</AttributePermissions>
</DimensionPermission>
Testing Dimension Security
After you define dimension security on a role, you must make sure that it works as you
expect it to. Because you are the administrator of the database, if you issue queries to the
cube under your own credentials, you always have enough privileges to see the data
without applying dimension security. To test this role, you can use the cube browser in BI
Dev Studio or SQL Server Management, which enables you to browse the data as a particu-
lar user or a member of a certain role. To test the role, follow these steps:
1. Access the cube browser by double-clicking the Warehouse and Sales cube in the
Solution Explorer and moving to the Browser tab in BI Dev Studio, or by right-click-
ing the cube name— Warehouse and Sales —in the Object Explorer of the SQL Server
Management Studio and choosing Browse from the resulting menu.
2. Select the Choose User icon, which will show you the Security Context Properties
dialog for the Warehouse and Sales cube.
3. In the resulting box, select the role from which you want to use to browse the data,
as shown in Figure 36.11.
After you have chosen the role, you can browse the cube as if you were a member of that
role. Drag the Store Sales measure and drop it in the data area of the browser, and then
drag the Customer dimension and drop it in the rows area. When you drill down into the
Customer dimension, as shown in Figure 36.12, you can see that the USA member has only
two children— CA and OR —because WA is restricted. Notice also that the total value for
Store Sales in the USA member equals Store Sales in CA plus Store Sales in OR
(because we set the VisualTotals property of the State Province attribute to TRUE ).
NOTE
You don't have to use the tools shipped with Analysis Services to test roles. If you
have client application that enables you to specify a connection string property, you can
define the Roles connection property as:
Roles=YourRoleName;