Overview: Three methods for unit testing the results being returned from an SSAS data source, using the security context as if you were the end user.
In the real world we usually don’t possess access to user passwords. Therefore, in order to test “as if” you were an end user, you need to impersonate them in order to test that the SSAS security role is set up properly. Following are techniques for impersonating a user while unit testing.
Method #1 for Impersonating SSAS Role Security: EffectiveUserName in SSMS
This technique is appropriate when you have an MDX query (such as an SSRS dataset query) & you want to execute the query while impersonating a specific end user.
First, launch SQL Server Management Studio. Connect to your Analysis Services instance.
Before clicking the Connect button, choose Options. Under Additional Connection Parameters, enter the user you’re testing. The syntax is as follows:
EffectiveUserName=”DOMAIN\UserName”
Now that you have a query window open in SSMS with the EffectiveUserName in the connection string, paste the MDX query in & carry on conducting your test. The query results should (hopefully!) match your expectation based on how the role is set up in SSAS.
Sidenote: as long as you have that query window open, if you were to fire up SQL Profiler you can see your existing session. The TextData column shows which SSAS role is applied to the EffectiveUserName, which is quite handy to verify.
The only bad thing about method #1 is SSMS has a hard time letting go of the connection, even after you close the query window & disconnect/reconnect in Object Explorer. You might need to close & reopen SSMS to get it to behave.
Method #2 for Impersonating SSAS Role Security: EffectiveUserName in Excel
This method is much like what’s discussed above, except we are using the end-user environment (i.e., Excel) rather than SSMS.
First, in a new workbook let’s go to Existing Connections & select one that points to your SSAS database you wish to test:
At the Import Data dialog box, select Properties. (Don’t worry, if you miss this part you can always update the data connection properties later from the “Data” ribbon.)
Under the Definition tab, within the Connection string, add a semicolon plus the user you’re testing. The syntax is as follows:
;EffectiveUserName=DOMAIN\UserName
Method #3 for Impersonating SSAS Role Security: Browse Cube in SSMS
First, launch SQL Server Management Studio. Connect to your Analysis Services instance. With this method, we won’t need the Options button like we did above.
Expand the tree to select the cube you are testing. Right-click the cube & choose Browse.
In the cube browser window, click the toolbar item to “Change User.”
You’ll be given a Security Context dialog box. In this situation we want to select “Other user” and type in the DOMAIN\UserName we wish to test. Another option is to test the Role as a whole.
At this point you can drag & drop fields into the cube browser window. With any luck, you’ll immediately be able to verify by the dimensional attributes that get displayed that the SSAS role security is working as intended.