Dynamic dimenssion security

I am trying to pass the username to a vb function in the allowed members set of a dimenssion. For some reason it is not getting to the function. All of the other parameters are being received as I am logging them in the event log.

Any ideas on why it looks like the username is not being picked up at all would be great.

Thanks in advance.

Chris

Chris,

I'm assuming you are using the UserName() function? Can you post the MDX expression you have in the allowed members set definition?

You might try creating a test calculated member using some or all of the MDX expression you are using for that parameter just to make sure it is resolving in the manner you think it should...

Dave F.

|||

Thanks for the reply Dave. In the allowed members set I am doing a call to a vb strored procedure which takes in as parameter a username and level in the hierarchy it is providing security for. The stored procedure then executes a sql query on a view which returns an mdx string providing a set of members for a specific level.

If you execute the stored procedure from a query in management studio for example

SecurtiySP.GetOrgMembers(USERNAME, 7)

This then does return correctly. But when reporting services executes a query, it doesnt seem to pass the users username at all. I believe it may be to do with the account RS is running as and that it isnt part of AD and cant impersonate other accounts, so I will have a look next tuesday and see what I can find.

|||

Yes, the problem could be with having Reporting Services in the mix (or any other mid-tier application). If Reporting Services is running on a separate server, you would have to set up Kerberos in order for Reporting Services to impersonate the authenticated users in relation to Analysis Services. Also the server running Reporting Services will have to be trusted for delegation. More on this here:

http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx

|||

Hi Michael,

Thanks for the reply. I have since found the problem. It seems accoring to this article http://support.microsoft.com/kb/913667 that we needed to run sp1 and the hotfixes. It is now passing the correct username :)

Chris