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 find NOT FOR REPLICATION option

» Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison

Can't find NOT FOR REPLICATION option

Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.
Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison
|||Thanks Paul, this is actually my first post about this, I just copied
the message SQL gave me when trying to setup a snapshot replication. I
looked all over Enterprise Manager but couldn't find an option "NOT FOR
REPLICATION". I guess you can only do it via SQL script.
FYI, I am just trying to reset a test DB back to production state every
night at midnight (after they play in the test DB all day). Nothing
should replicate to the production server......only from the
productions server to the test server. Hope I am headed in the right
direction.
Thanks again,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
you are on the right track. What you are doing is called a nosync
initialization. You need to script out the tables and create them on the
subscriber before initializing and change the article properties so as to
not drop the table on the subscriber during a name conflict. The setting
"NOT FOR
REPLICATION" can be done in EM. It is on an identity's column in table
design, but can equally be done in a script.
Snapshot replication is good for this, but 'Database Shipping' can equally
be used and also takes users and permissions that your application might
require. It also saves you from adding articles as the application develops.
HTH,
Paul Ibison
|||Can I just create a blank database on the test server and restore the
last backup over top of the blank DB? Then just change each IDENITY
column to NOT FOR REPLICATION?
Thanks for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
if you mean a nosync initialization, this doesn't work, as when your
subscriber is ultimately being used as a test machine, your identity values
will clash. If the identity columns are used for PKs then you will end up
getting PK violations. This is because replication will not increment the
identity value when records are added to the subscriber and DBCC CHECKIDENT
cannot be used to reseed the identity value. I'd consider shipping database
backups for your scenario. You could alternatively avoid these problems by
using merge or transactional with queued updating subscribers, but there
will be a lot of unnecessary work going on behind the scenes on your
production server which you really don't want.
HTH,
Paul Ibison
|||So replication will not increment the identity value when records are
added to the subscriber? What value, if any, gets put in the identity
field during replication?
How does the shipping database option work?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
true - replication doesn't increment the internal identity value - it stays
as the initial seed. Don't confuse this with the population of the column
during replication which works OK, essentially doing an identity insert.
This setting is really used for updating subscribers - snapshot or
transactional, or merge.
By database shipping I was thinking of doing a backup of the production
database, copying it over to the test server and restoring it there. You'll
need to create your own jobs to implement this, but it is not difficult. In
fact if you do a search for log-shipping scripts you can hack these to do
what you want, which is essentially very similar.
HTH,
Paul Ibison
|||Thanks Paul, I will Google log-shipping scripts and see if I can get
that working.
Thanks again for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Can't find NOT FOR REPLICATION option

Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison|||Thanks Paul, this is actually my first post about this, I just copied
the message SQL gave me when trying to setup a snapshot replication. I
looked all over Enterprise Manager but couldn't find an option "NOT FOR
REPLICATION". I guess you can only do it via SQL script.
FYI, I am just trying to reset a test DB back to production state every
night at midnight (after they play in the test DB all day). Nothing
should replicate to the production server......only from the
productions server to the test server. Hope I am headed in the right
direction.
Thanks again,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
you are on the right track. What you are doing is called a nosync
initialization. You need to script out the tables and create them on the
subscriber before initializing and change the article properties so as to
not drop the table on the subscriber during a name conflict. The setting
"NOT FOR
REPLICATION" can be done in EM. It is on an identity's column in table
design, but can equally be done in a script.
Snapshot replication is good for this, but 'Database Shipping' can equally
be used and also takes users and permissions that your application might
require. It also saves you from adding articles as the application develops.
HTH,
Paul Ibison|||Can I just create a blank database on the test server and restore the
last backup over top of the blank DB? Then just change each IDENITY
column to NOT FOR REPLICATION?
Thanks for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
if you mean a nosync initialization, this doesn't work, as when your
subscriber is ultimately being used as a test machine, your identity values
will clash. If the identity columns are used for PKs then you will end up
getting PK violations. This is because replication will not increment the
identity value when records are added to the subscriber and DBCC CHECKIDENT
cannot be used to reseed the identity value. I'd consider shipping database
backups for your scenario. You could alternatively avoid these problems by
using merge or transactional with queued updating subscribers, but there
will be a lot of unnecessary work going on behind the scenes on your
production server which you really don't want.
HTH,
Paul Ibison|||So replication will not increment the identity value when records are
added to the subscriber? What value, if any, gets put in the identity
field during replication?
How does the shipping database option work?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
true - replication doesn't increment the internal identity value - it stays
as the initial seed. Don't confuse this with the population of the column
during replication which works OK, essentially doing an identity insert.
This setting is really used for updating subscribers - snapshot or
transactional, or merge.
By database shipping I was thinking of doing a backup of the production
database, copying it over to the test server and restoring it there. You'll
need to create your own jobs to implement this, but it is not difficult. In
fact if you do a search for log-shipping scripts you can hack these to do
what you want, which is essentially very similar.
HTH,
Paul Ibison|||Thanks Paul, I will Google log-shipping scripts and see if I can get
that working.
Thanks again for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Cant find diagram property

Hi,

i set up an diagram with crystal reports and Visual Basic 2005.
Everythink works fine, but I got in trouble with a font size property of the so called "datalabel". I did not find the property where I can set the fontsize to a smaller one. To demonstrate my problem I attached a picture with the meant label surrounded.

Can anybody help me?Ok, I asked the supprt of crystal. It is not possible with the visual studio integrated version, you have to update to XI :(

Can't find answer to this anywhere

A fairly straightforward question:
When using rs.CreateFolder to create a folder, is it possible to set the
"Inherit security from parent" property to false? How would I do this. What
is the property called? I have tried "InheritSecurity" but that did not
work...On Mon, 18 Apr 2005 03:57:27 -0700, "DBA72"
<DBA72@.discussions.microsoft.com> wrote:
>A fairly straightforward question:
>When using rs.CreateFolder to create a folder, is it possible to set the
>"Inherit security from parent" property to false? How would I do this. What
>is the property called? I have tried "InheritSecurity" but that did not
>work...
You might want to look at UpdateDeleteAuthorizationPolicy. I don't
know of any example syntax to go beyond what is in BOL, which isn't
much.
Andrew Watt
MVP - InfoPathsql

Can't connect to server

I reinstall sql server develope edition. Then I try to connect the server from another computer, it shows below infomation. But in server property, it allows remote connection. I don't know whether it was caused by reinstall. Before reinstall, I can connect to the server, but it's edtion is group version. And edition for client is also group version. Any one can help me? Thank you very much!

TITLE: Connect to Server

Cannot connect to *.*.*.*.

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

BUTTONS:

OK

Hi,

Have you checked if the Named Pipe and TCP/IP are enabled? Ensure firewall rules are in proper manner!!1

Also refer NET DDE and NET DDE DSDM and SQL Browser Service are started in Control Panel -> Administrative Tools-> Services

Hemantgiri S. Goswami

|||Now I'm reinstalling again. If issue still exists after that, I'll take your suggestion. Thank you!|||After reinstall, issue has been solved.|||

Hi,

Thanks for the information , nice to know that your issue has been resolve.

Hemantgiri S. Goswami