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

Defining Dimension Security Using the User Interface

Now that you know what properties the AttributePermission object has, you can define
a sample permission that doesn't allow a user to see customers located in the state of
Washington. To do this, specify the following set expression in the DeniedSet property:

{[Customer].[State Province].&[WA]}

Each of the following methods enables you to define dimension security:

. Manually constructing and sending Data Definition Language (DDL) by using an

ALTER command within an Execute request

. Using Analysis Management Objects (AMO) in a custom application to define

attribute permissions

. Using the Role Designer user interface, provided by SQL Server Management Studio

or BI Dev Studio, to define attribute permissions

The following are the steps that you need to perform in BI Dev Studio to set dimension
security on the Customer dimension:

1. In BI Dev Studio, open the FoodMart 2008 project.
2. In the Solution Explorer, double-click the TestRole.role role to open the Role
Designer, or right-click the Roles node and select Create a New Role from the
contextual menu.
3. Select the Dimension Data page in the Role Designer, and ensure that the Basic tab
displays. You can use the Basic tab to either deselect members that you want to hide
from the users of the role or to select members that you want to allow the users of
your role to see.
4. In the Dimension drop-down list, choose the Customer database dimension. (If you
want to define dimension security differently for dimensions of different cubes, you
can select a cube dimension.)
5. In the Attribute Hierarchy drop-down list, choose the State Province attribute hier-
archy.
6. In the Basic tab, deselect the WA member.
7. You can use the Advanced tab to specify the sets of members allowed or denied to
users of that role. When you check or uncheck members in the Basic tab, the
Advanced tab automatically updates. Figure 36.10 shows the Advanced tab of the
Role Designer.

When you save your work and deploy the project, BI Dev Studio sends the DDL to the
server, which then alters the cube and saves the DimensionPermission object. Listing 36.1
shows the portion of DDL related to the defining the dimension security.