CODEWITHSUNDEEP
excelssasbusiness-intelligenceolapcube
Pirvu Georgian
Pirvu Georgian

Reputation: 691

How to avoid changing the Excel connection string for SSAS Role

I have implemented the Dynamic Security in our BI-SSAS Projekt using a User-Dimension that is updated from Active Directory. I created SSAS-Roles and inside I restrict the users to see only the specified branches. As Front-End they are using Excel. The problem is that Excel does not use as default the SSAS-Role created so I need to modify the conection string and add the following Property:Extended Properties="Role=PowerUsers" . How can we use the role created without having to edit the conection string from excel ? Could be this Excel connection string property added as default?

enter image description here

Upvotes: 1

Views: 1285

Answers (2)

GregGalloway
GregGalloway

Reputation: 11625

Why doesn't Excel use the right role if you remove Roles= from the connection string? Is the user in question an SSAS admin? Are you 100% sure? If you connect Profiler to SSAS and watch while that user connects it should list the roles the user belongs to. If the roles say *,PowerUsers,Readers then the asterisk means the user is an admin. If you remove him as an admin you won't need to specify Roles on the connection string.

You might also install BIDS Helper and run the Roles Report on your cube. It will expand the security groups in case that user is in a group you weren't aware of.

Upvotes: 1

MicrorFrank
MicrorFrank

Reputation: 156

There is not directly way in the standalone Excel pivot table, but there are a couple of potential approaches you can try: 1) Save the connection string (file) in a shared folder where your users can access it all, you don't have to modify anything in Excel once you specify that file(folder), but you still have to modify that file in the folder. (not good but should be okay) 2) 'IF' you are using SharePoint excel service, the thing is simple: just create a SSS, all can be managed under SharePoint SSS

Upvotes: 0

Related Questions