Roles in SQL Server Analysis Services (SSAS) are fundamental for implementing security and controlling access to data and objects within a multidimensional or tabular model. By assigning users and groups to specific roles, you can grant or deny permissions at various granularities, ensuring that only authorized individuals can access sensitive information or perform specific operations.
This document provides a comprehensive guide to understanding, creating, and managing roles in SSAS. We will cover the different types of permissions, how to assign them, and best practices for role-based security.
Types of Roles
SSAS supports different types of roles to accommodate various security requirements:
Database Roles: These roles grant permissions at the database level. They are the most common type and are used to control access to the entire Analysis Services database.
Account Roles: These roles are used for provisioning and managing administrative access to the Analysis Services instance itself, rather than specific databases.
Object Roles (Implicit): While not explicitly created as a separate "type," permissions on individual objects like cubes, dimensions, and measures can be managed within database roles, effectively creating object-level access control.
It's crucial to understand the difference between data permissions (what data users can see) and object permissions (what actions users can perform on SSAS objects).
Creating Roles
Roles are typically created and managed using SQL Server Management Studio (SSMS) or by deploying model projects that define roles.
Using SQL Server Management Studio (SSMS)
Connect to your SSAS instance in SSMS.
Navigate to the desired database.
Right-click on the Roles folder and select New Role....
The Role Properties dialog will open.
Using Visual Studio (for Tabular and Multidimensional Models)
When developing an Analysis Services project in Visual Studio, you can define roles directly within the project. This is often the preferred method for model-driven deployments.
In Solution Explorer, right-click on the Roles folder.
Select Add New Role.
Configure the role's properties as described below.
Configuring Permissions
Once a role is created, you need to define its permissions. SSAS offers granular control over access to various components.
Database-Level Permissions
These permissions determine what actions a role member can perform on the entire Analysis Services database.
Read Definition: Allows members to view the metadata and structure of the database but not to process or query data.
Read Data: Allows members to query the data within the database. This is essential for end-users using reporting tools.
Read All: Grants both Read Definition and Read Data permissions.
Process: Allows members to process (refresh) the database and its objects. This is typically for administrative or ETL roles.
Create Database: Allows members to create new Analysis Services databases.
Control: Grants full control over the database, including altering, dropping, and managing permissions. This is a high-level administrative permission.
Administrator: Grants full administrative privileges over the Analysis Services instance, including managing databases and server configurations.
In the SSMS Role Properties dialog, these are found under the General page.
Object-Level Permissions
For more fine-grained control, you can grant or deny permissions on specific objects like cubes, dimensions, hierarchies, and mining structures.
These permissions are configured on the Objects page in the SSMS Role Properties dialog.
Read: Allows viewing the object's definition and data.
Read Definition: Allows viewing only the object's metadata.
Read Data: Allows querying data related to the object (e.g., cube data).
Read Security: Allows viewing the security settings for the object.
Full Control: Grants complete control over the object.
When setting object-level permissions, remember that deny permissions always override grant permissions.
Cell-Level Permissions
Cell-level security (CLS) allows you to restrict access to specific cells (combinations of dimension members) within a cube. This is crucial for scenarios where different users or roles should see different data values, even within the same cube.
CLS is configured on the Cell Data page in the SSMS Role Properties dialog. You define an MDX expression that evaluates to the members that the role should be able to see.
Example MDX for CLS:
IF
EXISTS(
SELECT
{ [Measures].[Sales Amount] } ON 0,
[Customer].[Customer Geography].[Country].MEMBERS ON 1
FROM [Adventure Works]
WHERE
(
[Measures].[Sales Amount],
[Customer].[Customer Geography].[Country].&[United States]
)
) THEN
[Measures].[Sales Amount]
ELSE
NULL
This MDX snippet restricts access to the [Sales Amount] measure. Users in this role will only see sales data for countries that are not the [United States]. For the United States, the cells will appear blank (NULL).
For tabular models, cell-level security is typically handled using DAX row filters within the role definition.
Adding Members to Roles
Once a role is defined, you add users or Windows security groups to it.
In the SSMS Role Properties dialog, go to the Members page. You can add Windows accounts (e.g., DOMAIN\UserName or DOMAIN\GroupName) directly.
If you are managing roles within a deployed model project, you might assign members through the project's deployment wizard or by updating the membership post-deployment.
It is highly recommended to assign permissions to Windows security groups rather than individual users. This simplifies management, especially in large organizations.
Managing Role Properties
Beyond permissions and members, roles have other properties that can influence their behavior:
Session Context: Allows you to override user credentials for specific queries or operations executed by the role.
Permissions: As detailed in the configuration section, this is the primary property defining what the role can do.
Default Member: For dimensions, you can specify a default member for the role, which is automatically selected when users query data.
Advanced Topics
Consider these advanced concepts when designing your SSAS security model:
Row-Level Security (RLS): While CLS restricts specific cells, RLS restricts entire rows of data based on user identity or group membership. This is more common in tabular models using DAX.
Dimension Security: This allows you to restrict access to specific members of a dimension (e.g., a sales manager only sees data for their region). This is configured on the Dimension Data page in SSMS.
Discretization: Control how numeric measures are grouped for security purposes.
Role-Based Reporting: Integrate SSAS roles with reporting tools like Power BI or SQL Server Reporting Services (SSRS) to ensure users only see data they are authorized to access.
Dimension security is configured on the Dimension Data tab. For each dimension, you can specify an MDX expression that defines which members are visible to the role. For instance, you could create a role for "Regional Sales Manager" and limit their view of the [Geography] dimension to members representing their specific sales region.