Granting Dimension Access
By default, a database role in Microsoft SQL Server 2005 Analysis Services (SSAS) has read permissions on all dimension members in each cube to which the database role has access permissions. However, an administrator of the database can restrict the dimension members that a database role is permitted to view, and can grant read/write permissions to some or all the members of any dimension in the cubes to which the database role has access permissions.
After the database role has permission to access a cube, access permissions to dimensions in that cube are inherited from the permissions that are set on the dimension at the database level unless you explicitly set different permissions for the dimension within a particular cube. If a cube that is based on a single database dimension has multiple cube dimensions defined, the permissions that are specified for the database dimension apply to all the cube dimensions, unless the permissions are overridden for one or more of the cube dimensions. For example, suppose you have a cube that has three cube dimensions based on a single database dimension. You can grant a database role read access to selected members of the dimension at the database level, and then override those permissions at the cube level and grant read/write permissions to one or more of the three dimensions at the cube level.
Setting Read or Read/Write Permissions to a Dimension
To set permissions to a database role, a user must be a member of the Analysis Services server role or a member of an Analysis Services database role that has Full Control (Administrator) permissions.
To give a database role read or read/write permission to a dimension
In SQL Server Management Studio, connect to the instance of Analysis Services, expand Roles for the appropriate database in Object Explorer, and then click a database role (or create a new database role).
Click Dimension Access in the Select a Page pane, locate the dimension in the Select Dimension Set list, and then select Read or Read/Write in the Access list for the dimension.
Warning
If you configure database role permissions by using Analysis Management Objects (AMO), any reference to a cube dimension in a cube's DimensionPermission attribute severs the permission inheritance from the database's DimensionPermission attribute. For more information about AMO, see Analysis Management Objects (AMO).
In addition to specifying read or read/write dimension access permissions to a database role, you can define the specific attribute hierarchies and members within the dimension to which role members are allowed access. By default, members of a role with cube access have at least read access to view all attribute hierarchies within the dimension and all of their members. By default, members with at least read access to an attribute member have at least read access to all cube cells related to the attribute member. You can limit cell access to specific cells.
For More Information:Granting Custom Access to Dimension Data, Granting Cube Access, and Granting Custom Access to Cell Data