SQL Server Analysis Services Server Security




By:
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.

SQL Server Analysis Services Security Tab in Management Studio

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.

SQL Server Analysis Services Data Source Properties Impersonation Information
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.

Last Update: 4/7/2016




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools