Controlling Access to Analysis Services Cube



You need to add team members to the TFSWarehouseDataReader role prior toso they can view, refresh, or makeMS Excel reports that access data in the SQL Server Analysis Services cube.

Out of the box, users that are permitted to access the Team Foundation relational DB and the Analysis Services cube are set when Team Foundation Server is installed. Out of the box, write access to the warehouse is controlled to a service account. Each tool adapter has write access to the data warehouse because it runs under this service account.

If you aim to grant team members access to the cube to author intelligences or PivotTable reports and PivotChart reports in MS Excel directly against the cube structure, the administrator for Team Foundation Server need to grant or revoke permissions on each team member's account as appropriate. Typically, you would grant team members read-only access to the Analysis Services cube but not the relational DB because the cube is a lot easier to use and has been optimized for reporting. A member who has permission to view all data in the warehouse for a particular team project has full access to all the data for all team project collections.

 

To grant or deny access to Analysis Services cube:

  1. On the data-tier server for Team Foundation, click Start > All Programs>click MS SQL Server 2008> click SQL Server Management Studio.

  2. In the Connect to Server dialog box and enter your server, select Analysis Services.

  3. In the Server name box, enter the name of the server where the Team Foundation Server data tier is located, and then click Connect.

  4. In MS SQL Server Management Studio, in Object Explorer, expand the server name node, expand the DBs node, expand the Tfs_Analysis node, and then expand the Roles node.

  5. Doubleclick the TFSWarehouseDataReader node.

  6. Click Add to add users or click Remove to delete users, and then follow the instructions on the screen. This adds and deletes users who have access to the whole cube.