OLAP Connectivity from Pivot Table in Excel - security permissions

Security permissions in OLAP is similar to normal OLTP based environment. Say if you are using Pivot Table object in MS Office Excel to display the reports using OLAP objects like Cubes. To achieve this it should connect to an OLAP Server and database to fetch the data. As per the configuration when it tries to refresh data in the pivot table, it internally checks whether the windows user from the current system has required access rights.

If the login that used in Excel do not have proper permission on the Cube then it gives error "Either the user, Domain\User, does not have access to the OLAP Database, or the database does not exist".

In case of having different SQL Server groups you should assign proper privileges to the user and put them in appropriate user group. Security credentials are essential once your OLAP data source becomes accessible over HTTPS because your data source is now potentially available to anyone who can browse your Web site. For example, a remote user can open Excel and connect a PivotTable to your OLAP data source simply by providing the URL for the Analysis Server and the name of the Initial Catalog.

Analysis Services implements cube security roles based on Windows user accounts. Basically, you create one or more local Windows NT user accounts on the server and assign them to a SQL Server database role. You then assign the database role to a cube role and assign it to the specific cube. The cube role essentially inherits the Windows NT user ID and password credentials. You can then add the user ID and password credentials to the OLAP data source connection string.

Also one OLAP data source may contain multiple cubes. You can connect to a specific cube by setting the OWC PivotTable control's <DataMember> XML tag value to a valid cube name (explained in the next section). This cube must be assigned to a role that corresponds to the credentials in the OLAP data source connection string.