Authorization in SQL Server Analysis Services (SSAS) is the process of determining what actions authenticated users can perform on SSAS objects and data. It ensures that sensitive data and administrative functions are only accessible to authorized personnel.
SSAS employs a robust security model that combines server-level roles, database-level roles, and object-level permissions to provide granular control over access.
Roles are fundamental to managing authorization in SSAS. They are collections of users, Active Directory groups, or Windows groups that are granted specific permissions.
Server roles are defined at the SSAS instance level and grant administrative privileges over the entire SSAS instance. Common server roles include:
Database roles are defined within individual Analysis Services databases. They control access to the objects and data within that specific database.
When creating a database role, you assign users or groups to it and then grant the role specific permissions on various securable objects within the database (e.g., cubes, dimensions, mining models).
Permissions define the specific operations that can be performed on securable objects. In SSAS, permissions are typically granted to roles.
Common permissions include:
Object-level security allows you to control access to individual securable objects within an Analysis Services database. These objects include:
You can grant or deny specific permissions to database roles on these objects. For example, a role might have read access to a specific cube but only read definition access to its dimensions.
Cell-level security (CLS) provides a more granular way to restrict access to specific data points (cells) within a cube. This is useful when certain data needs to be hidden from specific users even if they have general read access to the cube.
CLS is implemented using:
CLS can be defined at the cube level and is evaluated at query time.
Note: Implementing Cell-Level Security requires careful design of MDX expressions and can impact query performance if not optimized.
Authorization in SSAS is typically managed using the following tools:
Example of creating a role in SSMS:
-- Connect to your SSAS instance in SSMS
-- Right-click on the desired database, select Properties -> Security
-- Click 'Add' to create a new role
-- Assign members (users/groups) to the role
-- Navigate to the 'Members' tab to add users or groups
-- Navigate to the 'General' tab to set role name and grant permissions