Deny read access to a dimension

Question

Deny read access to a dimension

seaport

SSCrazy

Points: 2491
More actions
March 17, 2015 at 9:14 am

#147318

How do I deny read access to a dimension data, through either SSMS or XMLA?
It looks like I cannot deny access at the dimension level. I have to deny the read on each attribute separately by setting "AllowedSet" to be {}.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19126 More actions · Answer 1

seaport (3/17/2015)
How do I deny read access to a dimension data, through either SSMS or XMLA?
It looks like I cannot deny access at the dimension level. I have to deny the read on each attribute separately by setting "AllowedSet" to be {}.

If you click on the "Advanced" tab of a role's "Dimension Data" permissions, you would be able to specify the "Denied Member Set". Sometimes it is easier to create perspectives, depending on the need of course.

seaport SSCrazy Points: 2491 More actions · Answer 2

Martin,

Thanks for the quick reply

In the Advanced Tab, I still have to go through each attribute, right?

Also, I am trying to figure out how this "Advanced" Tab works. So far I find out that

1. add "{}" to Allowed member set box to un-select all members

2. add "{}" to Denied member set box to select all members

Is this right?

Martin Schoombee SSCoach Points: 19126 More actions · Answer 3

seaport (3/17/2015)
Martin,
Thanks for the quick reply
In the Advanced Tab, I still have to go through each attribute, right?
Also, I am trying to figure out how this "Advanced" Tab works. So far I find out that
1. add "{}" to Allowed member set box to un-select all members
2. add "{}" to Denied member set box to select all members
Is this right?

You'll have to list all attributes you want to deny access to, in the "Denied Member Set". Admittedly I haven't had the need to use this yet, but I don't think you need to list all the other attributes in the "Allowed Member Set". I could be wrong though.

The "{}" is an empty set with no attributes, and if specified in the "Allowed Member Set" effectively means you're not allowing access to any attributes.

Specifying members for the denied set should be pretty straight forward, with a similar syntax to creating a user-defined set in MDX. For instance, if you have a dimension called [Customer] with attributes [First Name], [Last Name], [Address] and you want to deny access to [Last Name] and [Address]...the syntax for the "Denied Member Set" would probably look something like this:

{[Customer].[Last Name].members} * {[Customer].[Address].members}

The above syntax (if my mind isn't failing me this morning), will exclude the individual members as well as the "All" member. If you need to expose the aggregate "All" member, the syntax will most likely be the following:

{[Customer].[Last Name].].[Last Name]} * {[Customer].[Address].[Address]}

You will of course have to test this to make sure it works as expected.