Configuring permissions for SSAS (2023)

By: Scott Murray | Updated: 2012-09-25 | Comments (9) | Related: > Analysis Services Security


Problem

You have created your first SSAS database and cube. Now you want to secure the cube and related dimensions. What techniques are used to apply permissions to the cube?

Solution

Securing Analysis Services does have some similarities to applying security to a SQL Server database in Management Studio; however, the options are definitely much more limited. First, all SSAS permissions center around a role concept; second, all role members must be Windows / Active directory based. SQL Server logins cannot be used! As such, security cannot be directly assigned to windows / active directory user or group. That user or group must be added as a member of a role. One other issue that may come up when working on a cube locally andUAC isturned on, security changes can not be completed unless Management Studio or Business Intelligence Development Studio (BIDS) is executed in "Run as Administrator" mode. The first step in setting up security begins during the SQL Server installation.

Administrator Configuration During SQL Server Installation

During the SQL Server installation, first time DBAs and DWAs are tempted to just click next.. next ... next. However, many of the installation screens contain vitally important selections that impact your department for the life of your installation. It is of the utmost importance to plan your installation well and ultimately test your installation results. One such planning item is determining who the SSAS Administrators will be. Once that decision is made, adding the appropriate users on the SSAS Account provision screen is as easy as adding the appropriate users as noted in the below screen print. These users will have full rights to ALL SSAS databases, cubes, and dimensions, and more importantly, the ability to add other users as full administrators to the instance or individually to single SSAS databases.

Configuring permissions for SSAS (1)

If additional administrators need to be added after the Installation, they can be added in Management Studio (SSMS). Depending on your level of experience, you may not realize that you can connect to Analysis Services (and Reporting Services for that matter) in Management Studio. First Open SSMS and then click Connect > Analysis Services.

Configuring permissions for SSAS (2)Configuring permissions for SSAS (3)

Once the connection is successful, right click on the Instance and Select Properties.

Configuring permissions for SSAS (4)

As noted below, within the SSAS Instance Properties, select security. On this window, new instance level administrators (including groups) can be added (or old ones can be removed). The normal Windows user selection box appears which allows you to search for and validate users. Now that the instance level security is setup, database and cube level security can be created.

Configuring permissions for SSAS (5)

Configuring permissions for SSAS (6)

Database and Cube Level Security

Database and cube level security can be maintained in either BIDS or Management Studio (if you are using a SSAS Project, which helps with deployment and version control, remember each deployment will overwrite the current security unless you use the deployment wizard (more details on the deployment wizard can be found at http://msdn.microsoft.com/en-us/library/ms174817(v=sql.105).aspx). My preference is to use BIDS, although the screens are very similar in either Management Studio or BIDS. In order to create a new role, simply right click on Roles and Select New Roles.

Configuring permissions for SSAS (7)

As shown on the below screen print, step 1 is todefine the Role Name in the properties windows; use a name which is both descriptive and meaningful. If the properties window is not visible, select View > Properties (or hit F4). Next, fill in the Role Description as needed. As important as the name is, the three other check boxesbelow the role description play a vital role in defining database level access to the members of this role. Each check box is described next:

  • Full control: This permission grants full access to this particular SSAS database. Members with this permission have similar access rights as the server role noted above. However, these permissions apply to this database only, and not to all the databases within the SSAS instance. Full control grants members access to add other users and to process and maintain databases, cubes, and dimensions.
  • Process database: This permission allows a member to process this database and its related cubes and dimensions.
  • Read definitions: This permission allows role members to read the database metadata. Granting this access, though, does not provide access to read the metadata of other objects within the database such as cube meta data or dimension metadata. This lower level meta data access will be discussed later in the article.

Often, none of these check boxes are checked for regular users of the cube.

Configuring permissions for SSAS (8)

Switching to the members tab allows the administrator to add specific users to the role. Membership can be added either by typing the individual users or by using the windows Advanced > Find option. Again, you can add active directory groups, local windows groups, domain users, or local users; however, SQL users cannot be used! The Add members processes is illustrated in the following screen print.Of course for manageability, using groups is recommended.

(Video) Implement Roles in a SSAS Tabular Cube

Configuring permissions for SSAS (9)

Moving on to the Data Source tab, intuitively, it would seem that a user would need access to the data source. However, granting access to the data source actually grants permissions to the underlying data sources of the SSAS project or database. Generally, this option should be set to None, unless users are utilizing data mining structures or links to external datasets.

Configuring permissions for SSAS (10)

Switching to the Cubes tab finally provides methods which will grant read data access to role members. If a SSAS database has multiple cubes, access is given on a cube by cube basis and is broken into the following three categories as described below and illustrated subsequently:

  • Access
    • None: Role members are not able to access this particular cube
    • Read: Users can read data from the cube, but not write data back to the cube. This option is most commonly selected
    • Read/Write: Users can read data from cube and write data back to the cube (for additional details on write back see: http://ssas-wiki.com/w/Articles#Write_Back )
  • Local Cube/Drillthrough Access
    • None: No Drillthough or local cube creation
    • Drill through: Drillthrough is permitted for this cube (see Cube > Actions tab)
    • Drill through and Local Cube: Drillthrough is permitted as is the creation of local cubes from the Server cube.
  • Process:
    • Role members are able to process this particular cube.

Remember, if access is not specifically granted to the cube, end users will not even see the cube from their client applications.

Configuring permissions for SSAS (11)
Configuring permissions for SSAS (12)

The Cell Data tab affords administrators the ability to granularly set read, read-contingent, and read/write permissions to role members. Once any of these options are enabled by selecting the appropriate check box, the administrator must enter a MDX expression which defines the cells which are available or restricted for the role members. Although, Cell Data permission restrict access to certain cells or cell ranges, these restrictions work hand and hand with the dimension permissions which will be subsequently discussed. Additionally, if certain cells are restricted, but other non-restricted calculated cells are derived from that restricted values, users may be able to make a determination of a particular value. To see additional details about this complex area of security see http://cwebbbi.wordpress.com/2008/05/20/cell-security-when-read-permissions-are-actually-read-contingent/.

Configuring permissions for SSAS (13)

The Dimension tabassigns privileges to role members at the dimension level. First, two options exists at the dimensions level, either Reador Readand Write. Roles whose access is set to none will not even see the dimension marked as such. Furthermore, roles can be granted Read Definition access which allows for the role members to read the metadata concerning thedimension. Similar to the Cubes Access Rights, granting Process rights to a role, allows the members to process that particular dimension. The next tab to the right of the dimension tab is the Dimensions data tab; moving from dimension security at thedimension level, the dimension data tab allows the administrator to restrict or deny access to certain attribute values. For example, if a sales group only should have access to sales data in their state geographic territory, then a role could be designed to only allow access the sales person's assigned states. As seen in the next illustration, two options for entering the dimension restrictions: the basic option which allows the selection of specific values to display or restrict. The advanced option relies on the entry of MDX queries for generating the list of available or restrict access points.

Configuring permissions for SSAS (14)

Configuring permissions for SSAS (15)

One caveat in the use of Dimension Data security; total rows will display the total for all values in the dimension even those that restricted which in turn could allow end users to determine the value of the restricted attributes. To have the end client reporting tools only rollup the unrestricted values, check the enable Visual Totals check box, displayed at the bottom of the following illustration, which will then only rollup the unrestricted values. Please note, this feature can cause slower performance.

Configuring permissions for SSAS (16)

Conclusion-Security

Implementing SSAS Security is as important as every other methods of restricting organizational data. AS a DBA/ DWA, we are ultimately responsible for providing not only the correct and appropriate data (all very quickly), but wealso must provide such data using a method and path which appropriately protects access to a SSAS database with such data. Therefore, SSAS allows us to setup the following security points:

  • who administers the database
  • who processes the databases, cubes, anddimensions
  • who can access the meta data about the cubes and dimensions
  • who can read data from the SSAS database
    • including drill through
    • dimension level and dimension data security
Next Steps

Related Articles

SQL Server Analysis Services SSAS Dimension Security Stored Procedures

Using a Parent Child Hierarchy in SQL Server to Implement a Custom Security Scheme

Add SQL Server Analysis Services Role Members via PowerShell or XMLA

Popular Articles

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

SQL Server Cursor Example

SQL Server CROSS APPLY and OUTER APPLY

(Video) SSAS Tutorials | How to Implement security in SSAS

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server DROP TABLE IF EXISTS Examples

SQL NOT IN Operator

How to tell what SQL Server versions you are running

Add and Subtract Dates using DATEADD in SQL Server

SQL Convert Date to YYYYMMDD

SQL Server Loop through Table Rows without Cursor

Using MERGE in SQL Server to insert, update and delete at the same time

(Video) SQL Server Reporting Service Configuration

Concatenate SQL Server Columns into a String with CONCAT()

Display Line Numbers in a SQL Server Management Studio Query Window

SQL Server Row Count for all Tables in a Database

Ways to compare and find differences for SQL Server tables and data

How to Get Current Date in SQL Server

Searching and finding a string value in all columns in a SQL Server table

SQL Server Database Stuck in Restoring State

(Video) Implementing SSAS Security
About the author

Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips

Article Last Updated: 2012-09-25

(Video) How to Implement Security with SSAS

FAQs

How do I give permission to SSAS? ›

In SQL Server Management Studio, connect to the instance of SQL Server Analysis Services, open the Databases folder, and select a database. Right-click Roles | New Role. Enter a name and description. In the General pane, make sure that the database permission check boxes are clear.

What are three types of permissions at the SSAS multidimensional database level? ›

There are three types of permissions: "Full control (Administrator)", "Process database" and "Read definitions".
  • Full Control - This permissions provides administrator level access on the database. ...
  • Process Database - A SQL Server DBA who is new to SSAS would get confused by the word "process".
Apr 7, 2016

How difficult is it to learn SSAS? ›

Depending on your programming background and your awareness of SSAS, it could take you anywhere from 14 hours to 14 months to learn. If you have zero programming and data skills, it could take longer.

How many server roles does ssas have? ›

Two types of roles are provided in SQL Server Analysis Services: The server role, a fixed role that provides administrator access to an instance of SQL Server Analysis Services. Database roles, roles defined by administrators to control access to objects and data for non-administrator users.

What is impersonation in SSAS? ›

Option. Description. Impersonate Account. Specifies the model use a Windows user account to import or process data from the datasource. The domain and name of the user account uses the following format:<Domain name>\<User account name>.

What is SSAS role? ›

Roles are used in Analysis Services to manage security for Analysis Services objects and data. In basic terms, a role associates the security identifiers (SIDs) of Microsoft Windows users and groups that have specific access rights and permissions defined for objects managed by an instance of Analysis Services.

How do I query a cube in SQL Server? ›

To analyze cube data
  1. Open Microsoft SQL Server Management Studio.
  2. On the Connect to Server page, select Analysis Services for Server type. ...
  3. Right-click Databases > Dynamics AX > Cubes > General ledger cube and then click Browse.
Nov 29, 2021

What is AAS cube? ›

Azure Analysis Services is a fully managed platform as a service (PaaS) that provides enterprise-grade data models in the cloud. Use advanced mashup and modeling features to combine data from multiple data sources, define metrics, and secure your data in a single, trusted tabular semantic data model.

Is SSAS an ETL tool? ›

SSIS stands for SQL Server Integration Services. SSIS is part of the Microsoft SQL Server data software, used for many data migration tasks. It is basically an ETL tool that is part of Microsoft's Business Intelligence Suite and is used mainly to achieve data integration.

Why do we use cube in SSAS? ›

An OLAP cube, also known as multidimensional cube or hypercube, is a data structure in SQL Server Analysis Services (SSAS) that is built, using OLAP databases, to allow near-instantaneous analysis of data.

Is SSAS a data mining tool? ›

In SSAS, the data mining implementation process starts with the development of a data mining structure, followed by selection of an appropriate data mining model. Once the model is built, it needs to be trained with a dataset which would be used as the source of prediction.

What level of security can be achieved in tabular model? ›

Table-level security. With table-level security, you can not only restrict access to table data, but also sensitive table name metadata. Set the metadataPermission property of the tablePermissions class in the Roles object to none.

How do I add users to SSAS roles? ›

To add members to the role, click the Members tab, and then click Add. Role members can also be added to a deployed model by using SQL Server Management Studio. For more information, see Manage Roles by using SSMS. In the Select Users or Groups dialog box, enter Windows user or Windows group objects as members.

What is AAS cube? ›

Azure Analysis Services is a fully managed platform as a service (PaaS) that provides enterprise-grade data models in the cloud. Use advanced mashup and modeling features to combine data from multiple data sources, define metrics, and secure your data in a single, trusted tabular semantic data model.

How do I query a cube in SQL Server? ›

To analyze cube data
  1. Open Microsoft SQL Server Management Studio.
  2. On the Connect to Server page, select Analysis Services for Server type. ...
  3. Right-click Databases > Dynamics AX > Cubes > General ledger cube and then click Browse.
Nov 29, 2021

Videos

1. Setting up SSRS Users 2.wmv
(Brian Swan)
2. Configuring HTTP access to Analysis Services
(Rob Kerr)
3. How To Grant Sysadmin Role/Permission/Privilege In Microsoft SQL Server
(Useful IT Videos^Tutorials)
4. How to Setup Scheduled SSAS Jobs
(Dashboard Gear)
5. SSAS Deploy Login Failure Issues
(Bhaskar Jogi (Go Online Trainings))
6. How to Create Login , Create User and Assign Permission to user in SQL SERVER
(Sql Training Sessions)
Top Articles
Latest Posts
Article information

Author: Otha Schamberger

Last Updated: 03/12/2023

Views: 5733

Rating: 4.4 / 5 (55 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Otha Schamberger

Birthday: 1999-08-15

Address: Suite 490 606 Hammes Ferry, Carterhaven, IL 62290

Phone: +8557035444877

Job: Forward IT Agent

Hobby: Fishing, Flying, Jewelry making, Digital arts, Sand art, Parkour, tabletop games

Introduction: My name is Otha Schamberger, I am a vast, good, healthy, cheerful, energetic, gorgeous, magnificent person who loves writing and wants to share my knowledge and understanding with you.