Setting Security Permission on Reports

The SharePoint project portal and the team reporting sites have to be configured separately from the rest of Team Foundation Server. As with Team Foundation Server, you can simply add server groups to the report site to provide easy administration. Or, you can opt to add individual users to provide a higher level of granularity in your security approach per report.

You can access the security setting for your site by first opening up the reports site. Right-click Reports in Team Explorer, and select View Report Site. Once the Report Manager site appears, click the Site Settings link on the upper-right corner of the page. At the bottom of the new page, you will have the option of configuring site-wide security, configuring item-level role definitions, and configuring system-level role definitions (as shown in Figure 16-15).

Figure 16-15

The Team Foundation Server data warehouse has an interesting security model. First, all Team Projects on the server share one data warehouse (as explained earlier in the chapter). If you want to tighten security, you can do periodical password resets on the data sources. In order for the reporting to work correctly, the service account (TFSSERVICE) needs at the very minimum access to the OLAP database. You can also willfully constrain what other accounts access the OLAP cube and other parts of the warehouse. To access Reporting Services, a user needs at least Contributor rights. One of the first steps you need to take when deploying Team System is making sure that the credentials have been set up on both the team portal and SQL Server Reporting Services. Otherwise, your users will only see a red x on the Reports node of the Team Project and will be unable to view reports. There are some scenarios where this is desirable, perhaps if you have built custom reports to assess the performance of each employee, for example. However, we would recommend that you should allow your team to view progress reports and bug reports to promote transparency and allow your team to perform self-corrections when needed.

As recommended in the security chapter (Chapter 4), you should consolidate your security within security groups, either on Active Directory or in Windows Server 2003 groups in Workgroup mode. Name the security groups as follows:

• ReportingServices

• AnalysisServices

• SQLServer2005

You can also set up a group for managing access to Windows SharePoint Services. The server administrators should typically be placed in the Content Manager group at the root level. Project administrators should also be placed in this group at the project level. Project contributors, on the other hand, should be placed in the Browser group at the project level. These permission levels allow you to control access to the Report Server.

At the project level, don't inherit security from the root. You should provide access directly to the cube for the cube browsers. If you are a project manager and are planning to use Microsoft Excel to create a pivot-table report, you should use the report authors permission level. Administrators have full access to warehouse data (such as when the last time the cube has been processed). They also have access to all the projects (and project data) on the data tier.

You can access the tables containing the role and permissions in the Team Foundation Server Warehouse by following these steps:

1. Open SQL Server Management Studio.

2. Connect to Analysis Services. Select your Team Foundation Server as the server name.

3. Expand the Database node.

4. Expand the TFSWarehouse database. This is the OLAP database that is used by the default reports in Team System, as well as any custom reports that require correlation of data and Excel pivot tables.

5. Expand the Roles node.

6. Double-click the TfsWarehouseDataReader role.

7. Click the Membership tab. You'll notice that the TFSREPORTS account has been designated as the main service account for SQL Server Reporting Services.