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 connect from Server A to B but B to A

I have two machines with SQL2000 on both machines.
Machine A is a member of the domain, Machine B is in a workgroup with
the same name as the domain.
Using Enterprise manager I can connect from Machine B and manage the
SQL server on machine A.
When trying to connect from Machine A to manage SQL server on Machine
B I get an error 'SQL Server does not exist or access denied'.
I have double checked username/password several times.
Client Network Utility are set to TCP/IP and Named Pipes on both
machines.
Ping works fines. I can also access shared folders from A to B and
vice versa.
What else could be the problem?Can you connect via SQL Login.
If you're connecting via Windows Login, be sure the account has the same
name and password on both side. Also, check to see if BUILTIN\<account> is
allowed access on serverB. If not, take a look at 'sp_grantlogin' in book
online and give it access.
-oj
"Tosch" <tosch_nospam@.swissonline.ch> wrote in message
news:9hbl51pm68gln1gmqp2ckpk5h1d7a5q7r4@.
4ax.com...
>I have two machines with SQL2000 on both machines.
> Machine A is a member of the domain, Machine B is in a workgroup with
> the same name as the domain.
> Using Enterprise manager I can connect from Machine B and manage the
> SQL server on machine A.
> When trying to connect from Machine A to manage SQL server on Machine
> B I get an error 'SQL Server does not exist or access denied'.
> I have double checked username/password several times.
> Client Network Utility are set to TCP/IP and Named Pipes on both
> machines.
> Ping works fines. I can also access shared folders from A to B and
> vice versa.
> What else could be the problem?
>|||I have tried connecting with SQL Login, no luck. I even tried sa and I
could not connect.
On Mon, 11 Apr 2005 13:54:05 -0700, "oj" <nospam_ojngo@.home.com>
wrote:

>Can you connect via SQL Login.
>If you're connecting via Windows Login, be sure the account has the same
>name and password on both side. Also, check to see if BUILTIN\<account> is
>allowed access on serverB. If not, take a look at 'sp_grantlogin' in book
>online and give it access.|||Hi
What happens if you try to do a telnet to server B using the port specified
in server B's TCP/IP Clinet configuration? By doing this, you can determine
if server B listen's properly on the correct port.
Regards
Steen
Tosch wrote:[vbcol=seagreen]
> I have tried connecting with SQL Login, no luck. I even tried sa and I
> could not connect.
>
> On Mon, 11 Apr 2005 13:54:05 -0700, "oj" <nospam_ojngo@.home.com>
> wrote:
>

Can't connect from Server A to B but B to A

I have two machines with SQL2000 on both machines.
Machine A is a member of the domain, Machine B is in a workgroup with
the same name as the domain.
Using Enterprise manager I can connect from Machine B and manage the
SQL server on machine A.
When trying to connect from Machine A to manage SQL server on Machine
B I get an error 'SQL Server does not exist or access denied'.
I have double checked username/password several times.
Client Network Utility are set to TCP/IP and Named Pipes on both
machines.
Ping works fines. I can also access shared folders from A to B and
vice versa.
What else could be the problem?
Can you connect via SQL Login.
If you're connecting via Windows Login, be sure the account has the same
name and password on both side. Also, check to see if BUILTIN\<account> is
allowed access on serverB. If not, take a look at 'sp_grantlogin' in book
online and give it access.
-oj
"Tosch" <tosch_nospam@.swissonline.ch> wrote in message
news:9hbl51pm68gln1gmqp2ckpk5h1d7a5q7r4@.4ax.com...
>I have two machines with SQL2000 on both machines.
> Machine A is a member of the domain, Machine B is in a workgroup with
> the same name as the domain.
> Using Enterprise manager I can connect from Machine B and manage the
> SQL server on machine A.
> When trying to connect from Machine A to manage SQL server on Machine
> B I get an error 'SQL Server does not exist or access denied'.
> I have double checked username/password several times.
> Client Network Utility are set to TCP/IP and Named Pipes on both
> machines.
> Ping works fines. I can also access shared folders from A to B and
> vice versa.
> What else could be the problem?
>
|||I have tried connecting with SQL Login, no luck. I even tried sa and I
could not connect.
On Mon, 11 Apr 2005 13:54:05 -0700, "oj" <nospam_ojngo@.home.com>
wrote:

>Can you connect via SQL Login.
>If you're connecting via Windows Login, be sure the account has the same
>name and password on both side. Also, check to see if BUILTIN\<account> is
>allowed access on serverB. If not, take a look at 'sp_grantlogin' in book
>online and give it access.
|||Hi
What happens if you try to do a telnet to server B using the port specified
in server B's TCP/IP Clinet configuration? By doing this, you can determine
if server B listen's properly on the correct port.
Regards
Steen
Tosch wrote:[vbcol=seagreen]
> I have tried connecting with SQL Login, no luck. I even tried sa and I
> could not connect.
>
> On Mon, 11 Apr 2005 13:54:05 -0700, "oj" <nospam_ojngo@.home.com>
> wrote:

Can't connect from Server A to B but B to A

I have two machines with SQL2000 on both machines.
Machine A is a member of the domain, Machine B is in a workgroup with
the same name as the domain.
Using Enterprise manager I can connect from Machine B and manage the
SQL server on machine A.
When trying to connect from Machine A to manage SQL server on Machine
B I get an error 'SQL Server does not exist or access denied'.
I have double checked username/password several times.
Client Network Utility are set to TCP/IP and Named Pipes on both
machines.
Ping works fines. I can also access shared folders from A to B and
vice versa.
What else could be the problem?Can you connect via SQL Login.
If you're connecting via Windows Login, be sure the account has the same
name and password on both side. Also, check to see if BUILTIN\<account> is
allowed access on serverB. If not, take a look at 'sp_grantlogin' in book
online and give it access.
--
-oj
"Tosch" <tosch_nospam@.swissonline.ch> wrote in message
news:9hbl51pm68gln1gmqp2ckpk5h1d7a5q7r4@.4ax.com...
>I have two machines with SQL2000 on both machines.
> Machine A is a member of the domain, Machine B is in a workgroup with
> the same name as the domain.
> Using Enterprise manager I can connect from Machine B and manage the
> SQL server on machine A.
> When trying to connect from Machine A to manage SQL server on Machine
> B I get an error 'SQL Server does not exist or access denied'.
> I have double checked username/password several times.
> Client Network Utility are set to TCP/IP and Named Pipes on both
> machines.
> Ping works fines. I can also access shared folders from A to B and
> vice versa.
> What else could be the problem?
>|||I have tried connecting with SQL Login, no luck. I even tried sa and I
could not connect.
On Mon, 11 Apr 2005 13:54:05 -0700, "oj" <nospam_ojngo@.home.com>
wrote:
>Can you connect via SQL Login.
>If you're connecting via Windows Login, be sure the account has the same
>name and password on both side. Also, check to see if BUILTIN\<account> is
>allowed access on serverB. If not, take a look at 'sp_grantlogin' in book
>online and give it access.|||Hi
What happens if you try to do a telnet to server B using the port specified
in server B's TCP/IP Clinet configuration? By doing this, you can determine
if server B listen's properly on the correct port.
Regards
Steen
Tosch wrote:
> I have tried connecting with SQL Login, no luck. I even tried sa and I
> could not connect.
>
> On Mon, 11 Apr 2005 13:54:05 -0700, "oj" <nospam_ojngo@.home.com>
> wrote:
>> Can you connect via SQL Login.
>> If you're connecting via Windows Login, be sure the account has the
>> same name and password on both side. Also, check to see if
>> BUILTIN\<account> is allowed access on serverB. If not, take a look
>> at 'sp_grantlogin' in book online and give it access.