SQL Server Analysis Services User Security

By: Siddharth Mehta

Overview

As all the users should not be administrators, we need to setup access for the rest of the users on the server. This is managed by setting up a database role for the users. Database roles define the detailed permissions for users as well as data security. In this section we will demonstrate how to setup users for a SQL Server Analysis Services (SSAS) database role.

Explanation

SSAS database roles are specific to a particular database. Assuming that you have at least one SSAS database available on the server, logon to SQL Server Management Studio (SSMS) and navigate to the role the database in the Object Explorer. Under the selected database you should be able to find the Roles folder. Right-click the Roles folder and select the "New Role" menu option. I have AdventureWorks sample cube installed on the server, and the below is the corresponding interface.

We can specify the role name, description and permissions for the role. There are three types of permissions: "Full control (Administrator)", "Process database" and "Read definitions".

1. Full Control - This permissions provides administrator level access on the database. This is different from server level administrator that we configured in the previous chapters. Server level admins have admin access on all the databases hosted on the server, which database admins necessarily do not have.
2. Process Database - A SQL Server DBA who is new to SSAS would get confused by the word "process".  In SSAS, "processing" means the set of tasks where SSAS performs data related operations on the dimensions and measures as per the design. There are different kinds of process operations, which also affect the kind of operation on the database objects. This permission is almost equal to a database owner privilege for the relational engine.
3. Read Definitions - This is the minimum permission required for anyone to connect to SSAS. This permission allows the user to read the metadata and does not necessarily provide the permission to read metadata of all database objects like a cube or dimension, which has to be set explicitly for each of them.

Having specified the role details, the next tab - "Membership", allows us to specify the members / groups who would be a part of the SSAS database role. Add all the users who would be a part of the role being created.

Additional Information

• Processing database objects is a relatively new concept to SQL Server DBAs who would have always worked with relational databases. Consider reading this article to understand processing SSAS objects in more detail.