SQL Server Analysis Services Server Security

By: Siddharth Mehta

Overview

Having fine tuned basic SQL Server Analysis Services (SSAS) server configuration, the next logical step is to setup users access and security on the server. Mostly users are made part of active directory groups based on their roles in the project / system, and those groups are provisioned access on the server. We would look at how to setup server level access and security for users intended to perform the admin role.

Explanation

The first important step in setting up security is identifying the users / groups who would act as the SSAS admin group. This step happens during the installation. When you install SSAS, during the Analysis Services Configuration step, the user is asked to add the user account that should have admin privileges on the server. Assuming that you have added yourself as the administrator on the server during installation, we will look at the options to setup server level security.

Logon to SQL Server Management Studio (SSMS), right-click on the server in the Object Explorer window, select the Properties menu option and Security tab. This should bring up a window as shown in the below screenshot.

Administrators can add or remove users / groups who would have admin access on the SSAS server. Keep in mind that SSAS admin privileges would be for the corresponding instance. If you have multiple instances of SSAS installed, you can connect to each from SSMS and setup administrators for each instance.

After setting up the administrative privileges on the server, the next step is to provide the additional required privileges on the SSAS instance. SSAS executes some tasks in the security context of the service account and the rest of the tasks in the context of the user who is requesting the task. Typically there would be couple a of SSAS databases installed on the server, and each of them would have one or more data sources. The connection to the data source by default is impersonated using the service account. This can be modified from the data source properties based on privileges available to any particular account being impersonated, as shown in the below screenshot.

Additional Information

• There are few other privileges that SSAS and service account may need. Consider reading this article, to learn how to grant these privileges to SSAS.