Share via


Example 1—Explicitly Specifying an Allowed Set

In this example, a database role has the dimension security settings that are shown in the following table.

Attribute IsAllowed AllowedSet DeniedSet ApplyDenied VisualTotals

State

True

True

False

City

True

San Jose

True

False

Gender

True

True

False

The results of these security settings are the following:

  • Only the California member of the State attribute will be visible. (The allowed set for the City attribute only includes San Jose—a city in California. This means that the allowed set not only limits the City attribute, but also limits the visibility of the State attribute to only California.).
  • Only cities that existed in the dimension when the allowed set was defined for the City attribute will be visible. Newly added cities will not be visible.

Reviewing the Result Set

Based on these dimension security settings for the database role (and based on cube access to all cells), a query on all members returns the result set that is shown in the following table.

All Level State City Gender Sales Amount

All Offices

27300

California

12900

San Jose

4200

Male

2000

Female

2200

Depending on how a user needs to use the information in the result set, the result set shown in the previous table may be too limiting for the user. For example, the total for all sales for all offices includes the sales in states to which the database role member has no permission. Therefore, a member of this database role would not be able to see the breakdown of sales by state. Similarly, the total for all sales in California includes sales for cities to which the database role does not have permission to view. Therefore, a member of this database role cannot break down the sales total by city.

See Also

Tasks

Granting Custom Access to Dimension Data

Concepts

Example 2—Explicitly Specifying a Denied Set
Example 3—Using the Except Function to Exempt Members from a Denied Set
Example 4—Using the Exists Function to Exempt Members from a Denied Set
Example 5—Using the Exists Function to Specify an Allowed Set
Example 6—Using the Exists and Except Functions to Specify Allowed and Denied Sets

Help and Information

Getting SQL Server 2005 Assistance