Overview:
Oftentimes when developing a cube there will be a need to limit which dimensions and measures a user can view within the cube. For the purpose of this blog, the steps and processes needed to develop a solid security model are out of scope; however, I will offer four different techniques which can be used in order to test the cube roles once they are setup.
Ideas for testing roles come from my personal experience as well as from the book, "Expert Cube Development with Microsoft SQL Server 2008 Analysis Services" by Marco Russo (which by the way I highly recommend).
Technique One:
The first technique, which I feel is a good place to start testing, can be performed within the Browser tab of BIDS. From here connection string properties can be set by clicking on the "Change User" button within the toolbar. This button on the toolbar is highlighted below:
Technique Two:
Similar to technique one, there is also another place within BIDS to perform cube security testing. This can be done by clicking on the "Test cube security" link which shows up within the "Cell Data" tab of each individual role. The benefit to technique one and two are that they can be performed within BIDS while developing the cube.
Technique Three:
The third technique uses a tool that most of us are very familiar with, Excel. You can run a client tool like Excel as another user by right clicking and using the "Run As" option. Once the context of the program has been set you can then browse the cube by creating a connection to the cube as shown in the screenshots below.
Similarly, while connecting to the cube you could create a data connection using the test user’s credentials. You could then browse the cube to perform the necessary testing. Note: In some versions of Windows the "Shift" key needs to be held down while right clicking in order to view the "Run As" option.
Technique Four:
The fourth technique, which I admit I was unaware of, is to perform the testing through SQL Server Management Studio. When connecting to Analysis Services click on the "Additional Connection Properties" tab of the "Connect to Server" dialog box (if you are unable to view the tabs click the "Options" button). Once on the "Additional Connection Parameters" tab, you can manually set the Role and Username which will be used to access the cube. The "roles" property can be a comma delimited list, however, you must be an Administrator to use this property. Cube roles which had previously been setup can be used in the comma delimited list. Similarly, the "effectiveusername" property can be used to impersonate another user (domainusername). You must be an Administrator to impersonate another user as well.
