Forum FAQ: How do I impersonate another user to test the MDX query in SSMS?

  • Article

Question

There is a button "Change User" in Cube Browser, we can impersonate other user in "Security Context" dialog. But in SSMS, how do I impersonate another user to test the MDX query?

Answer

In SSMS, connect to SSAS server, type the Server name and then click button “Options”. Switch to “Additional Connection Parameters” tab, specify the user account for property “EffectiveUserName”:

EffectiveUserName=”UserAccount”

You can also test the user account with below query:

WITH MEMBER MEASURES.X AS

USERNAME

SELECT MEASURES.X ON 0

FROM [Adventure Works]

 

But note: Only SSAS server administrators can use the EffectiveUserName property. Since you are still using current windows account to logon to the SSAS server, you will access to SSAS server with your own credential, and EffectiveUserName property only works with your MDX query.

Comments

  • Anonymous
    April 14, 2011
    does this also effect dynamic dimension security where USERNAME-function is used? if i remember correctly, using EffectiveUserName does not have impact on the dimension-members you're allowed to see as you have to be server admin to use EffectiveUserName you always see all dimension-members could you clarify this?

  • Anonymous
    May 02, 2011
    Gerhard, You are right that we always see all dimension members even using EffectiveUserName because of the server admin login. In implementing dynamic security , you can write MDX query to test the security configuration with EffectiveUserName property.

  • Anonymous
    July 22, 2012
    I have this following error, please help. TITLE: Connect to Server

Cannot connect to SERVERNAME.

ADDITIONAL INFORMATION: The following system error occurred:  Incorrect function.  (Microsoft SQL Server 2008 R2 Analysis Services)