SSAS Role not Being Used

I recently ran into a situation where one of our users was seeing all the data in our SSAS tabular cube/model. Yes, they were in the AD group specified in the Role, but they were also not an SSAS Admin. It seemed like the role wasn't even being taken into account when the user logged in.

I couldn't find anything on the internets to pinpoint the issue, so I'm writing it up in case anyone else runs into this.

As far as I knew the only reason the role would get skipped was if the user was an SSAS Admin, which like I said they weren't.

Running a SQL Profiler of the SSAS instance and logging in to the cube as the user showed me a Session Initialize value of "*, model access" which means they had access to everything (*) and to our one role "Model Access" (if specifically selected).

If I ran it as me, an SSAS Admin, I got the same exact thing.

Running as a user who was not having access problems showed only access to "Model Access".

So how were they bypassing the role even though not specified as an SSAS Admin in the Analysis Server's properties?

Local Windows admin.

Somehow an AD group the user was in was included as a local admin on the Windows server of our SSAS service. And if you look in the SSAS Admin list the local Administrators group is not listed there, so it might not be the first thing you think of when troubleshooting this kind of access.

There is an Advanced Property in the Analysis Service instance's settings called Security > BuiltinAdminsAreServerAdmins. If you want to include all the local Admins to see all the contents of your SSAS cubes you can leave it enabled (true), but if those users are not the same users who should have full data access you will want to disable that option.

Bradley Schacht's blog post describes how to turn this off as well as the dangers of shutting it off before adding people/groups as SSAS Admins.

Hope this helps!

SSAS Data Connection in Excel - Unable to connect to server

I just ran into a very odd situation trying to connect a user's Excel 2013 to a SQL Server Analysis Server using Data Connections. When setting up the Data Connection, it was acting as if the server wasn't there - an "unable to connect to server" or "server not found" sort of thing. Needless to say, spelling was checked, basic network connectivity was checked, and SSAS connectivity was successfully checked on another computer.

Additionally, the local firewall was turned off, a different user logged in and tried (and failed), the computer could ping the server, and even a SQL Server Data Connection in Excel to the same server worked.

I finally did a Repair on Office 2013 in Add/Remove programs (or, now, Uninstall a Program), and that did the trick. There must have been something corrupt in the components that handled the SSAS Data Connections.

Hope this helps anyone else who runs into this.