Prologika Forums

I am trying implement a variation on a theme of Dynamic Dimension Security.  I am using the same code from the book (p.540 Applied Microsoft Analysis Services 2005) except for the sql query when I run it I get the following error

AccessViolationExecption was unhandled.  when I try to set the expr.ExpressionText = reader(0).toString() 

 

 

Public Shared Function GetCubeUserSubsidiaryMDXSet(ByVal userName As String) As [Set]

userName = userName.Substring(userName.IndexOf("\") + 1)

Dim conn As New SqlConnection("data source=wwiccube1;initial catalog=QuotaCubeFy09;Integrated Security=SSPI")

conn.Open()

' Create a command

Dim cmd As New SqlCommand()

cmd.Connection = conn

' Retrieve the regions assigned to the employee

cmd.CommandText = "Execute [cube].[userAllowedSubidiary_get]" & " '" & userName & "'"

' Get the regions as a reader

Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Dim expr As AdomdServer.Expression = New BLOCKED EXPRESSION

Dim resultSetBuilder As SetBuilder = New SetBuilder()

Dim tupleBuilder As New TupleBuilder()

While reader.Read()

expr.ExpressionText = reader(0).ToString()

Dim member As Member = expr.CalculateMdxObject(Nothing).ToMember()

tupleBuilder.Add(member)

resultSetBuilder.Add(tupleBuilder.ToTuple())

End While

reader.Close()

Return resultSetBuilder.ToSet()

End Function

What does the command return? The dataset returned should include the fully qualified member names. Note that in Listing 16.1 I construct the fully qualified name, such as [Sales Territory].[Region].&[<key>]

As a side note, you may find my two-part article about dimension data security useful. It discusses additional considerations for using dynamic security and demonstrates an alternative approach that uses a factless table and may perform much better.

I am also getting this error message.  I have created my own dynamic security stored procedure based upon your sample code and I am getting the same error on the line where the expressionText property is set.  I thought that maybe it was my code, so I pulled your solution from the SQL Mag article.  When I run the sample code through the debugger I am getting the same error message at the expressiontext line.  I have provided the error message that I am getting below and have italized the code which is throwing the error.  I of course know this works, but I have no idea what I am doing wrong and how to fix it.  Do you have any ideas?  Thanks for any help that you can give me. 

While reader.Read()

expr.ExpressionText = String.Format("[Reseller].[Reseller Name].&[{0}]", reader(0).ToString())

' Get the member

Dim member As Member = expr.CalculateMdxObject(Nothing).ToMember()  

System.AccessViolationException was unhandled
  Message="Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
  Source="msmgdsrv"
  StackTrace:
       at PFSetLastError(Int32 , PF_External , UInt16* , UInt16* , UInt64 )
       at PFSetLastError(Int32 in_hrError, UInt16* in_szNote, UInt16* in_szTag, UInt64 in_mskState)
       at Microsoft.AnalysisServices.AdomdServer.g_GetMDContext()
       at Microsoft.AnalysisServices.AdomdServer.Expression.set_ExpressionText(String expressionText)
       at UDM.Prologika.Extensibility.Security.GetSecurityFilter(String userName) in C:\Documents and Settings\leof\Desktop\Source\Source\Extensibility\Security.vb:line 83
       at ConsoleApplication1.Module1.Main() in C:\Documents and Settings\leof\Desktop\Source\Source\ConsoleApplication1\Module1.vb:line 11
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[ args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[ args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

Hm, I've just debugged it and it worked fine. Do this:

1. Change the LoginID column for record with employee ID 272 (Stephen Jiang) to your Windows logon id. Process the Dimension Security cube.

2. In BIDS, attach to the msmdsrv.exe and browse the cube under the External role.

3. Let me know what's reader(0).ToString() in the line that was giving you an error. It should return 115 so the expression text becomes [Reseller].[Reseller Name].&[115], which is a valid Reseller member.

I don't understand why it is not working fo me.  I am trying to write an MDX query using the function and I am getting an error.  What build of SSAS are you running?

select

Extensibility.GetSecurityFilter('adventure-works\michael9') on rows,

[Measures].[Reseller Sales Amount] on columns

from [Adventure Works]

Executing the query ...

Execution of the managed stored procedure GetSecurityFilter failed with the following error: Exception has been thrown by the target of an invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.

Internal error: An unexpected exception occured.

Internal error: An unexpected exception occured.

Execution complete

Works for me but I had to replace [Reseller Sales Amount] with [Reseller Sales-Sales Amount] as the Dimension Security cube discussed in the article doesn't have Reseller Sales Amount (see attached image).

I have somewhat solved my problem. 

Just to summarize, I do not get any errors when I actually use the classes and methods in the code when I construct it. Whenever I call any classes or methods from the Microsoft.AnalysisServices.AdomdServer namespace in Visual Studio, I do get an access exception error when I run my code through the debugger or call the function from another class library in Visual Studio.

Interesting enough, when I build my solution and attach the .dll to SSAS, the function does work correctly!

The dataset returned are fully qualified member names. It does not have much variation from the code in the the book.  the member names have been changed to protect the innocent.

this is a couple of rows the sproc returns. 

[Geography].[Subsidiary Code].&[03]
[Geography].[Subsidiary Code].&[04]
[Geography].[Subsidiary Code].&[10]

I have implemented my dynamic security function in a dev environment and have begun thorough testing of the function.  I am able to return sets with multiple tuples and when I get an error I return an empty set to Analysis Services ( Return MDX.StrToSet("{}") ).

I do have one question/issue though.  If someone enters a bogus key value then the function fails at the return value where the Set is returned to SSAS ( Return SetBuilder.ToSet() )

I guess the AdomdServer namespace validates that the tuples are legitimate before passing the set to the server.

 Do you know if there is a way to validate whether the tuples are legitimate before they are even added to the SetBuilder?  That way they could be tossed out.  Right now it is all or nothing based upon the fact that if the key value is bogus an exception is thrown and I return and emplty set to SSAS.

I would like to be able to replace bogus values with an empty.  ex { [Dim].[Dim].&[123], [Dim].[Dim].&[124], {} } 

Thanks for any help you can provide.

I can't verify this now but you can try MDXValue.ToMember() which presumably should error our if the member is incorrect.