Grant Team Members Access to the Analysis Services Cube
You must add team members to the TFSWarehouseDataReader role before they can view, refresh, or create Microsoft Excel reports that access data in the SQL Server Analysis Services cube.
By default, the groups and users who are permitted to access the Team Foundation relational database and the Analysis Services cube are set when Team Foundation Server is installed. By default, write access to the warehouse is restricted to a service account. Each tool adapter has write access to the data warehouse because it runs under this service account.
If you intend to grant team members access to the cube to author reports or PivotTable reports and PivotChart reports in Microsoft Excel directly against the cube structure, the administrator for Team Foundation Server must 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 database because the cube is easier to use and has been optimized for reporting. A team member who has permission to view the data in the warehouse for a particular team project has full access to all the data for all team project collections.
Required Permissions
To perform this procedure, you must be a member of the sysadmin security group on the server for the Analysis Services database for Team Foundation. For more information, see Team Foundation Server Permissions.
To add or remove access to the Analysis Services cube
On the data-tier server for Team Foundation, click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.
In the Connect to Server dialog box, in the Server type list, select Analysis Services.
In the Server name box, select or type the name of the server where the Team Foundation Server data tier is located, and then click Connect.
In Microsoft SQL Server Management Studio, in Object Explorer, expand the <server name> node, expand the Databases node, expand the Tfs_Analysis node, and then expand the Roles node.
Double-click the TFSWarehouseDataReader node.
In the Edit Role dialog box, in the Select a page pane, click Membership.
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.