OLAP call SP error

Question

I created a stored procedure with C# as a DLL and added it as an assembly in an OLAP database. I am trying to use one of the functions in the Dimension Allowed Member Set for a Role I created. The function returns a set of members. But whenever I try to check the syntax for the MDX, I get the following error:

Check MDX script syntax failed because of the following error:

An error occurred in the MDX script for the dimension attribute permission's allowed member set: Execution of the managed stored procedure FunctionNamefailed with the following error: Exception has been thrown by the target of an invocation.Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed..


I tried different Security Settings for the Assembly, including:

Impersonation Mode: Default
Permission Set: Safe

&

Impersonation Mode: Service Account
Permission Set: Unrestricted

But it doesn't make any difference.

Any ideas?  

Answer 1

Are you adding this function to SQL Server Analysis Services database (SSAS Instance) or SQL Server database (SQL engine instance)? If you're using this in SSAS (which I'm guessing because database engine doesn't expose Impersonation Mode as a declarative setting), you might have better luck posting in the SSAS forum. Let me know if you'd like the question moved there.

Cheers, Bob