Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Configuring permissions for SQL Server Analysis Services

MSSQLTips author Scott Murray By:   |   Read Comments (7)   |   Related Tips: > 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 and UAC is turned 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.

SSAS Install

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.

SSMS ConnectSSMS Connect

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

SSMS Properties

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.

Add Admin User


User Selection

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.

New Role

As shown on the below screen print, step 1 is to define 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 boxes below 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.


New Role General screen

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.

Datasource

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.

data source

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.

cube
drillthrough

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/.

cell data

The Dimension tab assigns privileges to role members at the dimension level. First, two options exists at the dimensions level, either Read or Read and 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 the dimension. 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 the dimension 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.

dimension 1

dimension 2

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.

visual totals

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 we also 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, and dimensions
  • 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


Last Update: 9/25/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Sunday, April 06, 2014 - 7:56:33 AM - Scott Murray Read The Tip

Jan

 

i would use profiler for ssas to determine which user is connecting to the cube from Excel?  it is likely who is logged onto that machine


Saturday, April 05, 2014 - 6:19:25 PM - Jan Read The Tip

Hi,

 

I set my role by you, but I have this problem - when I use Cube Browser with created role, I get only data that this role should see. But when I click Analyze in Excel, or connect via analysis connection (and the specified user), I can see all data.

 

Do you have any clue where the problem is?

 

Thank you, Jan


Tuesday, January 07, 2014 - 7:18:59 AM - Scott Read The Tip

I would first try to use the Test Cube Security option in bids and the change user options (to the domain user account... this is one of the buttons in the toolbar).  That way you can test the options which were selected.  Are you logged in still as the admin when you run these tests?  


Tuesday, January 07, 2014 - 6:16:42 AM - Beeram Read The Tip

Hi Scott,

I have created role and which has Read Definition permissions on the database and my domain account is added to be member of that role. I have read access to data sources and Cube . Also on CELL Data section, I have unchecked Read permissions / Read/Write and Read contingent check boxes. But when I query measure values using MDX queries, null values are retuned even though underlying tables have data and cube was processed. Can you tell me what could be the issue here?.  Thanks in advance.

Beeram


Wednesday, July 17, 2013 - 10:23:50 PM - Thai Read The Tip

ok!

Test

1) after configure see part data from Role. and Test

Log off >>and Login possiblility >> and open Excel >> connect to SSAS (craete give see part data).

Summary: if login only computer can see part data. when login other computer problem. can see all data .

 

2) test connect ohter computer

login >> open Excel >> connect SSAS (role) >> data see all. That wrongs. 

 

i think bug


Monday, February 11, 2013 - 11:25:56 AM - scott murray Read The Tip

If now roles are defined all have access.


Sunday, February 10, 2013 - 8:02:59 PM - Vugar Read The Tip

Hi Scott,

Thanksfor greate article.

I have created and deployed cubes. I can connect and browse with excel.

Problem:

when i use Integrated Security=SSPI;Persist Security Info=True I can see all ssas Databases/Cubes even though I have not defined any role for those databases. Why?

I could not find single article where the following described:  no role defined and Integrated Security=SSPI; is used to access Cube.

 

Thank you in advance,

//Vugar




 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.