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 Server 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.
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 Server. 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 Server, choose Start, All Programs, Microsoft SQL Server 2008, and then choose 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 choose 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, choose Membership.
Choose Add to add users or choose Remove to delete users, and then follow the instructions on the screen. This adds and deletes users who have access to the whole cube.