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
Hi, one question, in the installation guide in the section "Data-Tier Prerequisites" mentions this:
"On the data-tier server, add the account for installing Team Foundation Server (for example, TFSSETUP) to the local Administrators group."
Is that really necessary? My DBA is having issues with that point (my Data tier is going to be a cluster).
Another thing the guide mentions in the section "Data Tier Prerequisites for Cluster Deployment" is this:
"Use the Team Foundation Server service account (for example, Domain\TFSSERVICE) as the SQL service account."
Again, is that really necessary for a cluster?
Thanks and good luck with the blog, i was searching for this information for a very long time.
Thanks for the post.
The TFSSetup account needs SA role on the SQL databases to be able to create the TFS databases and the required roles/permissions for the TFSservice account. Also the TFSSetup account will be responsible for the TFS jobs be created on the Data tier. So it is recommended that the TFSsetup account be a built-in administrator on the SQL server, by default built-in administrators are granted with SA rights in SQL. If keeping TFSSetup account as an Admin is an issue, you could remove the local admin privileges after the TFS installation is done. Further, When you do any kind of servicing to TFS (ie,.. Repair the installation, SP installation or Hotfix installation) the TFSSetup account should be added to the admin group on the server.
With respect to your question on TFSService account, SQL cluster installation requires a domain account to run the clustered services, built-in system account is not an option, Refer: http://msdn.microsoft.com/en-us/library/ms143763(SQL.90).aspx.
Any chance of the same info for TFS2010?
Thanks for the post. Will get the info posted in few days.
Here are the two blog links that can help you understand the permission requirements:
I'm asked by DBAs why TFSService needs db_owner access to its content database in TFS 2010. I'd really appreciate if you could provide information on this.