How to: Change the Security Settings for the Data Warehouse for Team System
By default, the groups and users who are permitted to access the Team Foundation relational database and the Team Foundation OLAP database 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 allow end users access to the cube to author reports or Microsoft Excel spreadsheets directly against the cube structure, the Team Foundation Server administrator must grant or revoke permissions on the user's account as appropriate. Typically, you would grant users read-only access to the OLAP database, but not the relational database, because the OLAP cube is easier to use and has been optimized for reporting. A user who has permission to view the data in the warehouse for a particular team project has full access to all the data for that project.
Contact your Team Foundation Server administrator or your team project administrator to make sure that your user account has the permissions described in the following section.
To perform this procedure, you must be a member of the Team Foundation Administrators security group or have the server-level Administer warehouse permission set to Allow. For more information, see Team Foundation Server Permissions.
To add or remove access to the data warehouse
On the Team Foundation data-tier server, click Start, click All Programs, click Microsoft SQL Server 2005, 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 TFSWarehouse 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 Remove to delete users and follow the instructions on the screen. This adds and deletes users who have access to the whole cube.
If you want to manage security on a more detailed level (for example, by team project), you can build a new role that specifies permissions for individual data dimensions. For more information about how to set roles and permissions in SQL Server 2005 Analysis Services, see "Role Designer (SSAS)" on the Microsoft Web site.