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.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019