No Cube Found Please Try Again

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

Trouble

You lot have created your get-go SSAS database and cube. Now you want to secure the cube and related dimensions. What techniques are used to utilize permissions to the cube?

Solution

Securing Assay Services does have some similarities to applying security to a SQL Server database in Management Studio; nonetheless, the options are definitely much more limited. First, all SSAS permissions middle effectually a office concept; second, all role members must be Windows / Agile directory based. SQL Server logins cannot be used! Every bit such, security cannot be straight assigned to windows / active directory user or group. That user or group must be added equally a fellow member of a role. One other issue that may come when working on a cube locally and UAC is turned on, security changes can non be completed unless Management Studio or Business Intelligence Evolution Studio (BIDS) is executed in  "Run as Administrator" mode. The offset stride in setting up security begins during the SQL Server installation.

Administrator Configuration During SQL Server Installation

During the SQL Server installation, showtime time DBAs and DWAs are tempted to just click adjacent.. side by side ... next. However, many of the installation screens comprise vitally of import selections that affect your department for the life of your installation. It is of the utmost importance to program your installation well and ultimately test your installation results. One such planning item is determining who the SSAS Administrators will be. One time that determination is made, adding the appropriate users on the SSAS Account provision screen is as easy every bit 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 every bit full administrators to the instance or individually to unmarried SSAS databases.

If additional administrators demand to be added later 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. Start Open SSMS and then click Connect > Assay Services.

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

As noted beneath, within the SSAS Example Properties, select security. On this window, new instance level administrators (including groups) tin be added (or old ones can be removed). The normal Windows user option 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.

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, call back each deployment will overwrite the current security unless you employ the deployment wizard (more than 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 utilize BIDS, although the screens are very similar in either Management Studio or BIDS. In lodge to create a new office, simply right click on Roles and Select New Roles.

As shown on the beneath screen impress, step 1 is to define the Role Name in the properties windows; use a proper name which is both descriptive and meaningful. If the properties window is not visible, select View > Backdrop (or hit F4). Next, make full in the Role Clarification as needed. As important as the name is, the three other cheque boxes below the office 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 every bit the server office noted higher up. However, these permissions utilize to this database only, and non to all the databases within the SSAS example. 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 non provide access to read the metadata of other objects inside the database such as cube meta data or dimension metadata. This lower level meta information access will be discussed later in the article.

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

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

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

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

• Access
  • None: Role members are not able to access this detail cube
  • Read: Users can read information from the cube, merely non write information back to the cube. This selection is near commonly selected
  • Read/Write: Users tin read data from cube and write information back to the cube (for additional details on write back see: http://ssas-wiki.com/westward/Manufactures#Write_Back )
• Local Cube/Drillthrough Access
  • None: No Drillthough or local cube creation
  • Drill through: Drillthrough is permitted for this cube (encounter Cube > Actions tab)
  • Drill through and Local Cube: Drillthrough is permitted as is the creation of local cubes from the Server cube.
• Procedure:
  • Function members are able to process this particular cube.

Remember, if admission is non specifically granted to the cube, finish users will not even see the cube from their client applications.

The Cell Information tab affords administrators the power to granularly set read, read-contingent, and read/write permissions to role members. One time any of these options are enabled past selecting the appropriate check box, the ambassador must enter a MDX expression which defines the cells which are available or restricted for the function members. Although, Cell Data permission restrict admission to certain cells or prison cell ranges, these restrictions work hand and manus with the dimension permissions which will be subsequently discussed. Additionally, if sure cells are restricted, but other non-restricted calculated cells are derived from that restricted values, users may be able to brand a determination of a particular value. To come across additional details about this complex expanse of security run into http://cwebbbi.wordpress.com/2008/05/twenty/cell-security-when-read-permissions-are-actually-read-contingent/.

The Dimension tab assigns privileges to role members at the dimension level. Start, two options exists at the dimensions level, either Read or Read and Write. Roles whose admission is fix to none will not even see the dimension marked as such. Furthermore, roles can exist granted Read Definition access which allows for the part 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 information tab; moving from dimension security at the dimension level, the dimension data tab allows the administrator to restrict or deny admission to certain attribute values. For example, if a sales grouping but should have access to sales data in their state geographic territory, and then a part could be designed to only let access the sales person's assigned states. Every bit seen in the adjacent analogy, ii options for entering the dimension restrictions: the basic option which allows the selection of specific values to display or restrict. The advanced choice relies on the entry of MDX queries for generating the list of available or restrict access points.

One caveat in the use of Dimension Data security; total rows volition display the total for all values in the dimension even those that restricted which in turn could permit end users to determine the value of the restricted attributes. To have the terminate client reporting tools just rollup the unrestricted values, check the enable Visual Totals bank 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.

Conclusion-Security

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

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

Next Steps

• Some boosted reading on Permissions and Access list: http://msdn.microsoft.com/en-us/library/ms174786(5=sql.105).aspx

Related Articles

Popular Manufactures

About the author

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

View all my tips

Commodity Last Updated: 2012-09-25

No Cube Found Please Try Again

Source: https://www.mssqltips.com/sqlservertip/2776/configuring-permissions-for-sql-server-analysis-services/

Share this post