January 4, 2012 at 8:19 pm
Here is the situation I'm facing. I've got a cube with confidential information in it. It's on the same Analysis engine with a bunch of other cubes. I'm trying to restrict access to this particular cube. I've set a Denial role within the cube whereby certain individuals have no access (supposedly to this cube). When they access the Analysis engine through Excel (they DO have a login for the engine) they are able to pull down that cube and all it's data and dimensions. Am I doing something incorrectly or does Excel not look at Roles when it accesses the SSAS Cubes?
Ideally I wanted to just hide one member of a dimension....that didn't work so I moved to denying the entire dimension.. and that didn't work so I tried to deny the cube from the user...none of this seemed to work when accessed by Excel.
Suggestions or where to look for my error?
Donalith
January 10, 2012 at 2:37 pm
Donalith (1/4/2012)
Here is the situation I'm facing. I've got a cube with confidential information in it. It's on the same Analysis engine with a bunch of other cubes. I'm trying to restrict access to this particular cube. I've set a Denial role within the cube whereby certain individuals have no access (supposedly to this cube). When they access the Analysis engine through Excel (they DO have a login for the engine) they are able to pull down that cube and all it's data and dimensions. Am I doing something incorrectly or does Excel not look at Roles when it accesses the SSAS Cubes?Ideally I wanted to just hide one member of a dimension....that didn't work so I moved to denying the entire dimension.. and that didn't work so I tried to deny the cube from the user...none of this seemed to work when accessed by Excel.
Suggestions or where to look for my error?
Donalith
Is there a good reason why the users have access at the Analysis Services engine level?
Typically that's just Devs/Admins. In order to simply browse/query the cubes, end-users should just be given access to the individual cubes via roles. Even if the cube doesn't have security, you still should have at least 1 role granting access to a generic group like "Authenticated Users".
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
January 10, 2012 at 2:40 pm
Only the Admins have access to the cubes but they should have access to different cubes at different levels.
How does you set individual access with Roles on cubes? I've read some different tutorials but I haven't had much luck. No matter what permissions I set in the roles it seems that when an Admin connects via Excel the roles don't have any effect.
January 10, 2012 at 3:39 pm
Donalith (1/10/2012)
Only the Admins have access to the cubes but they should have access to different cubes at different levels.How does you set individual access with Roles on cubes? I've read some different tutorials but I haven't had much luck. No matter what permissions I set in the roles it seems that when an Admin connects via Excel the roles don't have any effect.
Unless I'm mistaken, if a user is granted the server administrator role on the Analysis Services server, they have access to all cubes. If you wanted to restrict access for these users, it would need to be done via dynamic security.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
January 10, 2012 at 3:43 pm
Yeah, that's what I've been getting into lately.
Thanks folks.
Donalith
January 11, 2012 at 5:14 am
well another option would be to remove users from the list of server-wide admins and just give them admin privileges via a cube role...
on the general tab of the role properties window, there's a check box for "Full Control (administrator)" under the "Set the database permissions for this role". Between that and the other options under this section, you *may* be able to find better flexibility to meet your needs without having to delve into dynamic security...cell-level security slows things down
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply