Hello,

Currently, we are using Excel as our client tool for reporting; however, I'm
running into a security issue.

For instance, we created a Sales report giving the total sales and
commission. The manager want to add security where the sales people cannot
see each sales revenue and commission. I've created dimension security roles
but it is not enforced through excel. I've tried using protected worksheet
function but the sheets cannot be refreshed which defeats the purpose.

So now I am seeking advice in testing a client tool that provide security
that is needed. The other alternative is to create a cube for each sales
person.

Any suggestions?

how have you setup the security?
applying security into your cube should be enough.

Excel will enforce the security, if the security is not applied its because
there is a problem in the definition of the security.
using another tool will not help you.

can you describe what you want to secure and what is your current result?

"SAM" <SAM (AT) discussions (DOT) microsoft.com> wrote

Jeje is right, a description of what you want to secure and what you
currently seeing would help.

There are two common issues that might relate to you. One is not setting
the visual totals option.

see: http://msdn2.microsoft.com/en-us/library/ms175366.aspx

The result of this is that the users can only see their own results at
the leaf level, but they can see the sum of all the results at higher
levels. This means that users could potentially figure out other members
data.

The other issue that can circumvent dimensional security is putting
ordinary users in the Olap Administrators group. Olap Administrators
always see all the data.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#Tf5VyOvGHA.4436 (AT) TK2MSFTNGP05 (DOT) phx.gbl>, willgart (AT) hotmail (DOT) com
says...

I implemented dimension security:

Filter([Employees].[Name].Members,[Employees].CurrentMember.Properties("Employee Login") = UserName
OR
[Employees].CurrentMember.Properties("Manager Login") = UserName)

What I am trrying to accomplish here is when the user open the excel
spreadsheet connected to the Cube, that it will only show their own data and
the entire dimension data for the other sales persons. When I test the role
in the cube browser it works but it does not do this in the excel spreadsheet.

"Jeje" wrote:

how can you tell if the uers group has been added the olap admin group?

"Darren Gosbell" wrote:

BTW, i'm using SSAS 2000

"Darren Gosbell" wrote:

on the Windows server himself, you have to go in the users & groups.
I'll found a security group called "OLAP Administrators"
verify what are the users into this group.

after this, verify if the users are members of you roles into AS2000
and not member of another role which can open the door to unauthorized
customers.

if the role works fine when you test it, the security is correctly applied
in the cube.

"SAM" <SAM (AT) discussions (DOT) microsoft.com> wrote

There are no other users outside of myself in the OLAP admin group. So I
removed myself and tested it in Excel and no change.

When I test the role prior to the change it worked and afterwards it worked.

Now when I browse the cube I still get access to all data the same way in
Excel. I'm a little confused on how this suppose to work.

When I test the role, I only see my data; however when I connect via Excel I
see everyone's data.



"Jéjé" wrote:

how many roles has been created in your AS database?
does your users are members of the secured role?
do you use a domain (NT or AD)?
do you enforce the security at the server side or client side (default)?
does your users are members of more then 1 role?

"SAM" <SAM (AT) discussions (DOT) microsoft.com> wrote

Through constant testing and then I had the Network Admin to remove from
adminstrator groups so I can make sure it works. It does; however, there is
always another issue waiting.

When I sent the spreadsheet to another user, a sales person with actual sale
revenue and commission to test - I receive this error:


Excel was unable to get necessary information about this cube. The cube
might have been reorganized or changed on the server.
Contact the OLAP cube administrator and, if necessary, set up a new data
source to connect to the cube.

Have you seen this before?


"Jéjé" wrote: