SQL Server Analysis Services Documentation

Managing Permissions

Managing permissions is a critical aspect of securing your SQL Server Analysis Services (SSAS) data. This document outlines how to grant and revoke access to databases, objects, and specific data using roles and granular security settings.

Understanding SSAS Permissions

SSAS employs a role-based security model. Permissions are typically assigned to roles, and users or Windows groups are then made members of these roles. This approach simplifies administration and ensures consistent access control.

Key Permission Concepts:

  • Database Permissions: Define the level of access a role has to an entire SSAS database (e.g., Read, ReadWrite, FullControl).
  • Object Permissions: Control access to specific objects within a database, such as cubes, dimensions, measures, and partitions.
  • Cell Permissions: Provide granular control over access to individual data cells within a cube, based on specific dimension attribute values.
  • Row-Level Security: Filters the data visible to a user based on their identity, often by using security tables or expressions.

Creating and Managing Roles

Roles are the primary mechanism for managing permissions in SSAS. You can create custom roles or leverage built-in roles for common scenarios.

Steps to Create a Role (using SQL Server Management Studio - SSMS):

  1. Connect to your SSAS instance in SSMS.
  2. In Object Explorer, right-click on the desired database and select Properties.
  3. In the Database Properties dialog, navigate to the Security tab.
  4. Click New Role.
  5. Enter a descriptive name for the role (e.g., Sales Analysts, Cube Administrators).
  6. In the General page, select the appropriate database-level permissions.
  7. Navigate to the Membership page and add Windows users or groups to the role.
  8. For more granular control, select the Cube Permissions or Cell Data Permissions tabs as needed.
  9. Click OK to create the role.
Note: When assigning membership, it's best practice to use Windows groups rather than individual user accounts to simplify management as users join or leave the organization.

Granting Database Permissions

Database permissions determine the overall access level to a specific SSAS database. The most common permissions are:

  • Read: Allows users to query data and browse objects.
  • ReadWrite: Allows read access plus the ability to update or delete objects, and process data.
  • FullControl: Grants complete administrative control over the database.
  • Process: Allows users to process (refresh) data in the database.

Example: Granting Read access to Sales Analysts

To allow the Sales Analysts role to read data from the AdventureWorksDW database:

  1. In SSMS, right-click on the AdventureWorksDW database and select Properties.
  2. Go to the Security tab.
  3. Select the Sales Analysts role.
  4. On the General page, check the Read permission checkbox.
  5. Click OK.

Configuring Object-Level Permissions

You can restrict access to specific cubes, dimensions, measures, or other objects within a database. This is useful for scenarios where different user groups should only see a subset of the available information.

Steps to Configure Object Permissions:

  1. In the Database Properties dialog (as described above), select a role.
  2. Navigate to the Cube Permissions or Dimension Permissions tabs.
  3. For each cube or dimension, assign permissions:
    • Read: Allows browsing of the object's metadata and data.
    • ReadContol: Allows browsing and controlling the object's properties.
    • None: Denies access to the object.

Implementing Cell and Row-Level Security

For the most granular control, SSAS supports cell and row-level security. This typically involves using MDX expressions or security tables.

Cell Permissions:

Cell permissions allow you to restrict access to specific cells within a cube based on attribute values. This is often used to hide sensitive financial data.

  • In the Role Properties, go to the Cell Data Permissions tab.
  • Select the cube and choose the desired permission level (e.g., Read, None).
  • You can define MDX expressions to dynamically determine cell access based on user context or data values.

Row-Level Security (using Security Tables or MDX):

Row-level security filters the data visible to a user based on their identity. This is commonly implemented by associating users with specific regions, departments, or customer segments.

  • Using Security Tables: Create a table that maps users/roles to the data they can access. This table can then be joined with your fact tables within the cube processing or via MDX.
  • Using MDX Expressions in Roles: Define MDX expressions within role properties that filter the data returned by queries based on the user's identity (e.g., using the USERNAME() function).
Tip: For row-level security, consider creating a dedicated security dimension or using a security table that is updated automatically as part of your data refresh process.

Revoking Permissions

To revoke permissions, simply remove users or groups from roles, or modify the permissions assigned to a role.

  1. Open the role properties in SSMS.
  2. On the Membership page, select users/groups to remove and click Remove.
  3. On other permission pages (General, Cube Permissions, etc.), uncheck the permissions granted.
  4. Click OK.

Best Practices for SSAS Security

  • Principle of Least Privilege: Grant only the necessary permissions to users and roles.
  • Use Roles Extensively: Avoid assigning permissions directly to users.
  • Leverage Windows Groups: Simplify membership management.
  • Regularly Audit Permissions: Review and update security configurations periodically.
  • Document Your Security Model: Keep a clear record of roles, permissions, and their intended purpose.

By effectively managing permissions, you can ensure the security and integrity of your SQL Server Analysis Services data while providing appropriate access to your business users.