Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 22, 2010

Introduction to Security in Analysis Services - Page 2

By William Pearson

Access Rights and Permissions

Analysis Services Roles allow administrators to define levels of security on objects in an Analysis Services database for different Windows users and groups. Each object can have a single permission associated with it per Role, and each Permission can have one or more Access Rights associated with it. In addition, a Windows user or group can be associated with more than one Analysis Services Role, giving us the capability to combine Permissions and Access Rights for complex security models in business intelligence applications.

Access Rights

The set of Access Rights available for Permissions associated with objects in an Analysis Services database is described in Table 1 below.

Access Right



Provides the ability to access metadata for an object. The following types of access are supported:

  • None denies access to the object.
  • Read allows members of the role to read from the object.
  • ReadContingent allows members of the role to read a cell value only if the user can access all the cells from which the value is derived. ReadContingent provides Read access for any cells specified by this permission that are not derived from other cells.

    For example, where the value of the Net Profit cell is calculated from the value of the Sales cell minus the value of the Costs cell, a user can read the Net Profit cell only if cell access is set to Read (or Write) for both the Sales and Costs cells.
  • ReadWrite allows members of the role to read from and write to the object.


Indicates whether members of the role can administer the object.

Administer permission gives members of the role complete access to all objects contained in the object.


Allows members of the role to browse the data in a mining model.


Gives members of the role permission to drill through from a mining model to the underlying data.


The AllowedSet permission defines the members of an attribute that a member of the role can view. For example, if the allowed set in [Customer].[CountryRegion] is {Canada}, then the members of the role have access to all the provinces and cities of Canada.

For a parent-child hierarchy, the allowed members are those defined by the set plus the ascendants of the parent-child hierarchy that exist with those members. If a member of a parent-child hierarchy is not in an allowed set, its children — other than the data members — are not accessible to the role. The data members are still accessible because they belong to the key attribute of the dimension.

The default, if no set is defined for the AllowedSet permission, is the set of all the attribute members.


Predict permission for a mining model gives members of the role permission to predict based on the mining model.


Determines whether members of the associated attribute that exist with other explicitly denied members cannot be viewed.


The DefaultMember permission defines the default member of the dimension. The default member affects the datasets returned by queries on cubes that include the dimension. When the dimension is not displayed on an axis, by default the dataset is filtered (that is, sliced) using the default member.


The DeniedSet permission defines the members of an attribute that a member of the role cannot view.


Determines whether access to any member of the attribute is permitted regardless of the settings for a level based on the attribute.

If the IsAllowed property is False for the granularity attribute on a dimension, setting VisualTotals on a dimension attribute results in null values for all of its members. For unary operators, when VisualTotals is False, each member is a rollup of all its children. If VisualTotals is set to True, each member is a rollup of permitted children.

The default for this property setting is True.


Process permission for an object gives members of the role the permission to process the object. It also grants permission to process all child objects within the object unless this permission is explicitly denied on a child object. Process permission does not grant members of the role access to the data or metadata of the object.


Indicates whether members of the role can read the metadata that defines the permission object. This property setting is inherited by objects contained in the object.


The VisualTotals permission for dimension data defines how data is aggregated for attributes.

This is an MDX expression returning True or False. If VisualTotals is False, data is aggregated on all members of attributes of the dimension regardless of whether they are visible to members of the role. If VisualTotals is True, data is aggregated only for those members of the granularity attribute of the dimension to which the role has read access. (For example, if Customer Name is the granularity attribute and VisualTotals is set to True for the City attribute, each city will be the aggregation of data for the customers to which the role has read access.

The default setting is False.

Table 1: The Access Rights Available for Permissions Associated with Analysis Services Database Objects (Source: MSSQL Server 2008 Books Online)

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM