Can't get Dynamic Security working ...

I'm trying to get dynamic security working using a member property which contains a list of usernames that are allowed to access that level of the hierarchy. I'm using the following setup:

Dimension: DynManager

Hierarchy: DynManager

Levels (in order): SVP, DVP, RVP, ROM, DDO, Store

Attributes: SVP -> SVP Access List, DVP -> DVP Access List, RVP -> RVP Access List, ... etc

When I run the following test query, I get the set back that I expect:

SELECT FILTER([DynManager].[DDO].Members, INSTR([DynManager].CurrentMember.Properties("DDO Access List"), "JJester")) ON 0 FROM BiCube

However, when I attempt to use the same FILTER expression in the role's "Allowed Member Set" expression, I get the following error message:

An error occurred in the MDX script for the dimension attribute permission's allowed member set: Query (1, 56) The 'DynManager' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

The DynManager dimension only has one hierarchy, so I don't understand where the error is coming from. However, I did what it says and changed my query to the following:

FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester"))

The syntax checks out but when I go to browse the cube as this dynamic role, I get the following error message:

The DynManager hierarchy is used more than once in the Crossjoin function

Can anyone please provide some insight!? Thank you!

Jeff

What is the attribute that you are setting this as AllowedSet on ? Is it DDO attribute ? If it is not DDO attribute, then the error can be because of it, but if it is, then the error doesn't look right. Are you on SP2 of AS ? I vaguely recall a fix in this area...

|||

Hi Mosha! Thanks for the quick reply. I just upgraded to SP2 this morning hoping it would clear up some issues ... but no luck.

I'm setting the "AllowedSet" on the SVP, DVP, RVP, ROM, and DDO attributes. All other attributes are set to "Select all members".

Thanks again,
Jeff

|||The expression that you provided above should only be set on DDO attribute. Please remove it from all other attributes.|||

My filter expression in the AllowedSet is different for each level of the hierarchy.

For DDO, it's:

FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester"))

For ROM, it's:

FILTER([DynManager].[ROM].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("ROM Access List"), "JJester"))

For RVP it's:

FILTER([DynManager].[RVP].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("RVP Access List"), "JJester"))

and on and on ....

Do you have any idea why my test query would work in SQL Server Management Studio, but then I get the "more than one hierarchy" error when I try to apply the same FILTER expression in the AllowedSet?

|||

Ok, I think I figured out the issue. The parameters in my filter expression are too specific (if that's even possible). I tried removing the Dimension name and it works. So, the new expression looks like this:

FILTER([DDO].Members, INSTR([DDO].CurrentMember.Properties("DDO Access List"), "JJester"))

Anyhow, thanks for your help Mosha!

sql

can't get alerts to fire!

Hi,
Hope y'all can help...
I've tried to get it to fire from a specific error # as
well as from a severity level. I have also done all the
troubleshooting tips on BOL. Still no dice. The job
itself executes, this I've checked. I turn the service on
& off, and that registers on the event log, so that's
working.
Thanks!Is the error you are defining the alert on appearing in the event log? =If not you will need to define the error to write to the log - see =sp_addmessage for details.
Mike John
"Ian" <anonymous@.discussions.microsoft.com> wrote in message =news:0c9801c3a0d6$c470acf0$a001280a@.phx.gbl...
> Hi,
> > Hope y'all can help...
> > I've tried to get it to fire from a specific error # as > well as from a severity level. I have also done all the > troubleshooting tips on BOL. Still no dice. The job > itself executes, this I've checked. I turn the service on > & off, and that registers on the event log, so that's > working.
> > Thanks!

Can't debug SP from Query Analizer with SP3a

Hi,
I am able to debug them correctly from VS.NET 2003, but trying from Query
Analizer gives me this error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
Any ideas?
Thanks in advance,
Juan Dent, M.Sc.
The first thing to do is to make sure that the account you are using can
execute the stored procedure indicated in the error message:
GRANT EXECUTE ON sp_sdidebug TO <your user account goes here>
Keith
"Juan Dent" <juan5@.dev.com> wrote in message
news:058F454E-48E0-4194-BBA2-21A953BF2B53@.microsoft.com...
> Hi,
> I am able to debug them correctly from VS.NET 2003, but trying from Query
> Analizer gives me this error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> Any ideas?
> --
> Thanks in advance,
> Juan Dent, M.Sc.
|||Yes I have done this, to no avail.
"Keith Kratochvil" wrote:

> The first thing to do is to make sure that the account you are using can
> execute the stored procedure indicated in the error message:
> GRANT EXECUTE ON sp_sdidebug TO <your user account goes here>
> --
> Keith
>
> "Juan Dent" <juan5@.dev.com> wrote in message
> news:058F454E-48E0-4194-BBA2-21A953BF2B53@.microsoft.com...
> on
>