(For more resources related to this topic, see here.)

Getting ready

The following prerequisite is essential for our recipe to continue the recipe:

• SQL Server 2012 Management Studio (SSMS).
• The AdventureWorks2012 database. We can obtain the necessary database files and database product samples from SQL Server Database Product Samples landing page (http://msftdbprodsamples.codeplex.com/releases/view/55330).

  These sample databases cannot be installed on any version of SQL Server other than SQL Server 2012 RTM or higher.

• Ensure you install the databases to your specified 2012 version instance.
• For this article I have created a new OLAP database using the AdventureWorksDM.xmlafile.

  Also, ensure that the user who is granting permissions is a member of Analysis Services server role or member of Analysis Services database role that has Administrator permissions.

How to do it…

The following steps are continued from the previous recipe, but I believe it is necessary to reiterate them from the beginning. Hence, this recipe’s steps are listed as follows:

1. Start the SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services instance.
2. Expand the Databases folder.
3. Choose the AdventureWorksDM database (created within the Getting ready section as previously mentioned) and expand the Roles folder.

   If you are reading this recipe directly without the previous recipes, you can create the necessary roles as per the Creating security roles(Intermediate) recipe.
4. Right-click on the role (here I have selected the DBIA_Processor role) to choose Role Properties.
5. Click on Cell Data on the Select a page option to present a relevant permissions list.

   In some cases, if you have observed that there is no option available in the Cube drop-down list in the Cell Data option, ensure you check that the relevant cube is set with appropriate Access and Local Cube/Drillthrough options by choosing the Cubes option on the left-hand side on Select a page.

   Refer to the following screenshot:

   

Now let us continue with the Cell Data options:

1. Click on Cell Data in the Select a page option to present a relevant permissions list.
2. Select the appropriate cube from the drop-down list; here I have selected the Adventure Works DW2012 cube.
3. Choose the Enable read permissions option and then click on the Edit MDX button. You will be presented with the MDX Builder screen. Then, choose the presented Metadata measure value to grant this permission.
4. Similarly, for the Enable read-contingent permissions option, follow the previous step.
5. Finally, click on the Enable read/write permissions option.

   

6. As a final check, either we can click on the Check button or the OK button, which will check whether valid syntax is parsed from the MDX expressions previously mentioned.
7. If there are any syntax errors, you can fix them by choosing the relevant Edit MDX button to correct.

This completes the steps to secure the data at the cell level using a defined role in the Analysis Services database.

How it works…

There are a few guidelines and some contextual information that will help us understand how we can best secure the data in a cell.

Nevertheless, whether the database role has read, read-contingent, or read/write permissions to the cell data, we need to ensure that we are granting permissions to derived cells correctly. By default, a derived cell obtains the relevant data from the other cells. So, the appropriate database role has the required permissions to the derived cell but not to the cells from which the derived cell obtain its values.

Irrespective of the database role, whether the members have read or write permissions on some or all the cells within a cube, the members of the database role have no permissions to view any cube data. Once the denied permissions on certain dimensions are effective, the cell level security cannot expand the rights of the database role members to include cell members from that dimension.

The blank expression within the relevant box will have no effect in spite of clicking on Enable read/write permissions.

Summary

Many databases insufficiently implement security through row- and column-level restrictions. Column-level security is only sufficient when the data schema is static, well known, and aligned with security concerns. Row-level security breaks down when a single record conveys multiple levels of information. The ability to control access at the cell level based on security labels, intrinsically within the relational engine, is an unprecedented capability. It has the potential to markedly improve the management of sensitive information in many sectors, and to enhance the ability to leverage data quickly and flexibly for operational needs. This article showed us just how to secure the data at the cell level.

Resources for Article:

Further resources on this subject:

• Getting Started with Microsoft SQL Server 2008 R2 [Article]
• Microsoft SQL Server 2008 High Availability: Installing Database Mirroring [Article]
• SQL Server and PowerShell Basic Tasks [Article]