TFS permissions and roles in SQL Server

  • Artykuł

A question I have seen often asked by SQL Server DBAdmins hosting Team Foundation Server Databases on their server - as well as by some TFS Admins: what are the exact server/database roles and permissions that different TFS Accounts should have at a minimum on the different TFS databases ? Mostly, the question is to TFSService and TFSReports: clearly, TFSSETUP is an account that needs pretty much all kinds of Admin permissions.

 I will try to shed some light on this:

  • First of all, there is currently no official Microsoft docs that describes such details. There is a reason for that: not only is manually touching the TFS (as well as Reporting Services and Sharepoint) databases, including permissions and roles, not recommended, but it can easily put you in an unsupported state (if you raise a support incident with MS). When TFS is installed, the configured database settings should work with all supported TFS operation and migration scenarios without requiring any manual touch of the databases (apart from backup/restores).

 

  • However, in troubleshooting scenarios it might be useful to check some basic things in the TFS-related databases:
    • Typically, TFSService is in the dbcreator and securityadmin Server Roles. If it is not, this is not necessarily an issue, but you might want to check if someone has done some manual configuration on SQL settings
    • TFSService should have User Mappings to the following databases (names of the Sahrepoint DBs might vary ):TFSActivityTracking, TFSBuild,TFSIntegration, TFSVersionControl, TFSWorkitemTracking, TFSWorkitemTrackingAttachements, TFSWarehouse STS_Config_TFS STS_Content_TFS
    • TFSService should be in the TFSEXECROLE for all TFS-related databases. This is owned by dbo, which in turn grants db_owner to the TFS Databases.
    • TFSReports should have a User Mapping on TFSWarehouse

This list is not exhaustive, but rather something you should expect to see on a properly installed TFS (2005 and 2008)

Please note the explicit reccomendation not to use these settings on a permanent basis or when servicing TFS!

The morale: apart from obvious issues, you should stay away of manually configuring TFS databases manually. For troubleshooting, try using TFS Best Practices Analyzer (BPA, https://msdn.microsoft.com/en-us/teamsystem/bb980963.aspx) first, it should detect most database/SQL logins configuration issues.