Requirements to implement cell-level security with OLAP Services (259348)


The information in this article applies to:

  • Microsoft SQL Server OLAP Services 7.0
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

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 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 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.

    OLAP Services Service Pack 2 (SP2) can be downloaded from the following Web site:

    http://www.microsoft.com/downloads/details.aspx?familyid=C4C935C7-4429-4172-9D54-C8A60927F372&displaylang=en

Modification Type:MinorLast Reviewed:7/31/2006
Keywords:kbinfo KB259348 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.