A user can be part of one or more roles in an Analysis Services database; the process to grant permissions across these roles is additive.
The SQL Server Analysis Services (SSAS) security is managed using roles, and the same is true for the AS database objects and data. By default, SIDs of Windows users and groups will have specific rights and permissions.
Again by default, the SSAS instance uses Windows Only authentication and no other authentication type. Within the Analysis Services there are two types of roles: server and database roles.
The server role is a fixed role that provides administrator access on the instance level. Similarly, the database role is defined by the administrators for access control to objects and data, which can be defined as read, read-contingent, or read/write purpose.
Note
The prerequisite for this recipe can be obtained from the Getting ready section of the Designing and creating security roles in an OLAP database recipe.
Perform the following steps:
Start SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services (SSAS) instance.
Right-click on the SSAS instance and choose Properties to open the Analysis Server Properties page.
Choose the Security option from the Select a Page pane that will present a list of server administrators as shown in the following screenshot:
Now let us work on the database level security settings that will dictate the data source security information.
Expand the
Databasesfolder, right-click on the AdventureWorksDM database, and then choose Properties.To change the Data Source Impersonation Info setting, click on the button with three dots; the Impersonation Information page will be displayed. As per the default settings, the radio button selection is set on Use the credentials of the current user.
For this recipe let us choose the Use the service account option.
Step 3 will bring about the changes immediately without having to restart the SSAS instance by changing the Data Source Impersonation Info setting to ImpersonateServiceAccount.
Click on OK to apply the changes on the AdventureWorksDM database.
The relevant Analysis Services instance properties for the security page are used to specify the Windows users and groups included as members of the server administrator role for that instance.
The appropriate options at this level are to add or remove the NT Users and Groups, in order to enable them as server administrators. This means that the specified groups and users will have the highest privilege to perform any administrative action on the instance level. This will help to perform server-level administrative functions using the SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT) that includes the database's creation or setting server level properties, and can be used for the programmatically administrative functions using Analysis Management Objects (AMO).
This server role cannot be deleted, permissions cannot be added or removed, and the specified user on this role is not an administrator for the instance of Analysis Services.
However, when it comes to the database level, the database properties dialog box will help us set the properties of a database. The data source impersonation information is the key factor to set for a relevant user to have the required privilege on a database-wide basis. This action specifies the default user credentials used to connect to data sources.
The impersonation options here will be specific to a Windows username (with password) service account, using credentials of the current user used to connect to the SSAS instance or default.
The database will be created as a separate object within the AS database and will only be applicable to the database in which that role is created. The permissions for this role may allow members to access or administer the database, in addition to to default access to objects and data.
By default, each permission has one or more access rights associated within that database, which in turn gives the permission finer control over access to that specified object in the database.