Article ID: 259348 - Last Review: July 31, 2006 - Revision: 3.4

Requirements to implement cell-level security with OLAP Services

View products that this article applies to.

This article was previously published under Q259348

SUMMARY

Microsoft SQL Server online analytical processing (OLAP) Services introduced a cell-level security feature in Microsoft SQL Server OLAP Services Service Pack 1.

This article discusses some caveats in implementing cell-level security in OLAP Services.

A white paper that describes the new cell-level security feature is available at the following Microsoft Web site:

http://www.microsoft.com/technet/prodtechnol/sql/70/maintain/cellsec.mspx (http://www.microsoft.com/technet/prodtechnol/sql/70/maintain/cellsec.mspx)

Back to the top

MORE INFORMATION

Considerations for Implementing Cell-Level Security

Cell-level security only works for users that are not members of the OLAP Administrators group. Members of the OLAP Administrators group have full permissions on all cells and the permissions cannot be overridden.
You must check to see if a user belongs to a role that grants permission to access the same cell. For example, if you work with the sample Foodmart database, the user Everyone is in the role AllUsers. You must remove the user Everyone from the AllUsers role before you test to see if cell-level security works. If there is more than one role that applies to a given cell, OLAP uses OR logic between them. So, if a user belongs to two roles and one role provides access to a cell while another role denies access to a cell, the user has access to the cell.
If OLAP Services Service Pack 1 (SP1) is installed, make sure that the user is not part of the Windows NT Administrator group. If you are using OLAP Services Service Pack 1 (SP1) and you have a user that is a member of the Windows NT Administrator group, but the user is not a member of the OLAP Administrators group, the user is denied access.

This problem is fixed in Microsoft SQL Server OLAP Services Service Pack 2 (SP2).

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
236315 (http://support.microsoft.com/kb/236315/ ) FIX:Users Cannot Access OLAP Cube from Clients After S Install
If OLAP Server is installed on a Windows 2000 computer, by default, all local users have access to all cells. This occurs because of behavior changes in the defaults of the Windows 2000 registry.

To resolve this issue, see the following article in the Microsoft Knowledge Base:
241088 (http://support.microsoft.com/kb/241088/ ) FIX: Registry Permission Difference w/ OLAP on Windows 2000
The Microsoft Knowledge Base article, Q241088, discusses the OLAPRegFix utility, which you can use to reset the OLAP related registry keys. The OLAPRegFix utility can be downloaded from the article and the OLAPRegFix utility also ships with OLAP Services service pack 2.