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
n_srikanth4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
<<
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/
0
n_srikanth4Author Commented:
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.
0
lcohanDatabase AnalystCommented:
You will need to use USERNAME() function to determine if CURRENT USER() is part/has access to that role and you could do that via a MDX query like:
<<
SELECT {} ON 0,
[testdim].[Group Name].members ON 1
FROM [cube1]
Where Filter( [testdim].[user name].[user name].members, [testdim].[user name].currentmember.name = username())
>>
lot more detail on that at link below.
https://social.technet.microsoft.com/forums/sqlserver/en-US/5548d250-997d-4ca8-8ede-3b8844fa9af0/mdx-for-current-user

You could also add a table with user name and associate role. Then when applying denied/allowed sets, use the MDX expressions to find the role associated with the user and then apply restrictions. E.g. IIF(IIF(User_Name = [DIM USER].[DIM USER].[Domain Name].CurrentMember,[DIM USER].[DIM USER].[Role].CurrentMember, "Admin") = "RestrictedRole", True, False)

True = Visible, False = Notvisible


You can also create a data-source on top of your cube with an additional property called "Roles".

E.g. DataSource = ;Initial Catalog = ; Roles=RestrictedRole

and then this data-source would apply restrictions automatically to anybody using it but this requires you to keep two copies of the data-source - one restricted and other not.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

n_srikanth4Author Commented:
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
0
n_srikanth4Author Commented:
Hi lcohan ,
 
Please share shed some light on my comments . I appreciate your inputs and feedback ?

Thanks,
SRK.
0
n_srikanth4Author Commented:
I've requested that this question be deleted for the following reason:

Not answered
0
n_srikanth4Author Commented:
Good
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.