Example 3—Using the Except Function to Exempt Members from a Denied 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		EXISTS (City..Members, EXCEPT (State.members, {CA}))	True	False
Gender	False			True	False

The results of these dimension security settings are the following:

• The denied set that is specified for the City attribute limits the visibility of descendants of newly added members of the State attribute through the EXISTS and EXCEPT key words.
• The Gender attribute is not visible because the IsAllowed property for this attribute is set to False.

Reviewing the Result Set

Based on these dimension security settings for the role (and based on cube access to all cells), the following result set is returned from the cube when all members are queried.

All Level	State	City	Gender	Sales Amount
All Offices				27300
	California			12900
		San Jose		4200
		San Francisco		4400
		Los Angeles		4300
	Washington			8700
	Oregon			5700

See Also

Tasks

Granting Custom Access to Dimension Data

Concepts

Example 1—Explicitly Specifying an Allowed Set
Example 2—Explicitly Specifying 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