asked on

SSAS Dynamic security not working.

Hi Experts ,

SSAS Dynamic security is working (when Roles is selected in browser) . Basically , there are three options when browsing the Cube from SSMS.
1.      Current User
2.      Other User (can enter LoginID here)
3.      Roles.
Precisely , security is not working for Option 1 and 2 , but working for Option 3 (mentioned above).

I understand that the security works always based on the role and it works apparently because the security definition is provided for the role in SSAS (in allowed member set).

Please Note : I have not created any role, I am using the default role in SSAS.

I am not sure how the security works when option 1 /Option 2 is selected in cube browser(Change Users) . Can you please shed some light on this issue ? Will appreciate.

I have a security document which explains how I have implemented the Dimension security (by role ) . Also , please see the screen shots for the Issues.

Your quick resolution to Option / Option 2 is greatly appreciated in this regard.

Thanks,
SRK.
Implementing-Dynamic-Cube-Security.doc
Security_Issues.docx

lcohan

<<
If a SSAS database has multiple cubes, access is given on a cube by cube basis and is broken into the following three categories as described below and illustrated subsequently:

•Access ◦None: Role members are not able to access this particular cube
◦Read: Users can read data from the cube, but not write data back to the cube. This option is most commonly selected
◦Read/Write: Users can read data from cube and write data back to the cube (for additional details on write back see: http://ssas-wiki.com/w/Articles#Write_Back )

•Local Cube/Drillthrough Access ◦None: No Drillthough or local cube creation
◦Drill through: Drillthrough is permitted for this cube (see Cube > Actions tab)
◦Drill through and Local Cube: Drillthrough is permitted as is the creation of local cubes from the Server cube.

•Process: ◦Role members are able to process this particular cube.

Remember, if access is not specifically granted to the cube, end users will not even see the cube from their client applications.

>>

https://www.mssqltips.com/sqlservertip/2776/configuring-permissions-for-sql-server-analysis-services/

n_srikanth4

ASKER

Hi lcohan,

I know that the security is working for me and I have shared the document (Implementing Dynamic Cube Security.doc) that explains how I have implemented the security.

Question : Only when "Role" is selected , the security works , but not works when I have logged in as "Current User" /"Other User" through Cube Browser , Why ?

Please provide me your inputs and solution. I appreciate your help.

Thanks,

SRK.

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

n_srikanth4

ASKER

Hi lcohan,

I am not bringing the user security table to the cube , Instead I am adding the dll externally to the cube calling this user security table through the stored procedure.

Please Note : Security is working but through role .
Requirement : Security should work work regardless of role for the logged in user

Here is the table that has definition of security for each logged in user.

LoginID MemberType MemberValue
JDA\1019885 dim_sales_item [Sales Item].[Hierarchy - Sales Item Hierarchy Tree].[Sales Item Hier Lvl1].&[1]&[1001671]

This info. is passed in to the stored procedure and then I will build a .dll (.Net Assembly) and bind this dll to the Cube externally.

This is the filter I am using to bind the Allowed Member set in DimensionData.

EXISTS(
{[Sales Item].[Sales Item Hier Lvl1].MEMBERS},
{StrToSet(OLAPSecurity.BuildSecureSet(UserName(),"dim_sales_item"))}
)
I am attaching the screenshot of the same , I have tested it is working only for the Roles option (Option 3).

It is not working when I select the other two options (Current User/Other UserID).

Please let me know if you understand my requirement and help me with the solution.

I don't want to keep two copies of the data-source as I don't want to tweak the data source every time.

Thanks,

SRK.
MDX_Function.JPG

n_srikanth4

ASKER

Hi lcohan ,

Please share shed some light on my comments . I appreciate your inputs and feedback ?

Thanks,
SRK.

n_srikanth4

ASKER

I've requested that this question be deleted for the following reason:

Not answered

n_srikanth4

ASKER

Good