It is a common request from DBAs that the least possible privileges are given to the service account on database. Here is a compilation of the permissions that are required on each database and at the server level. This document applies to Team Foundation Server 2008.
Note: These permissions are suitable only for day-to-day activates. The defaults are required while doing any kind of servicing to Team Foundation Server. By Default, the Service account is added with the “Sysadmin”,“Securityadmin” and “DBCreator” roles. To identify the database level role assigned for the service account please run the “TFSAdminutil Status” command (ref: http://msdn.microsoft.com/en-us/library/ms253191(VS.80).aspx ). Servicing TFS includes Patching, repair of the installation or performing any administrative task like changing account names or password.
TFSActivityLogging, TFSBuild, TFSIntegration, TFSVersionControl
(TFSWarehouseDataReader if TFSservice is used as the reports account as well)
Public, TFSEXEXCROLE,DB_datareader, DB_datawriter
Public, TFSEXECROLE, DB_owner
Apart from the databases that are created by the TFS installer, there are couple of jobs that gets created. They are: 1) TFSActivityLogging Administration job, 2) TFSBuild Team Build Administration job, 3) TFSIntegration Maintanance job, 4) TfsVersionControl Administration Job, 5)TfsWorkitemTracking Full Text Crawl job and 6) TfsWorkItemTracking Process identities job.
These jobs are mainly used as a cleanup maintenance tasks and are owned by the “SA” account by default. The ownership of these jobs can be safely changed to the “TFSService” account. All these jobs work fine with the service account after the modifications mentioned earlier.
Content Developed by: Arunrama
Reviewers: Lakhmins, Wendellp