Recently a question came up through a customer about exactly what permission were needed to SQLServer in order to install/configure Team Foundation Server.  One of the developers put together a list of exactly what the configuration process does to SQL and what permissions are required.  Of course, many people just install with admin access to SQL and everything just works but in more structured organizations, sometimes the DBAs are different people than the TFS admins and the DBAs want to know exactly what this TFS thing is going to do to their database and what permissions it needs to do it.  For those people, here is the explanation:

  • Membership is the serveradmin server role.  During TFS configuration we install new system messages. This requires membership in the serveradmin role, see: http://msdn.microsoft.com/en-us/library/ms178649.aspx.
  • ALTER ANY LOGIN, VIEW ANY DEFINITION server permission. During TFS configuration we enumerate SQL logins and create a SQL login for servicing account.
  • CONTROL on master. Here is a list of privileges that we need  on master:
    • CREATE ROLE – We need this permission to TFSEXECROLE role.
    • VIEW DEFINITION – We need this permission to see extended properties on the database.
    • ALTER – To install messages, we drop and recreate prc_Install*Messages stored procedures, such as prc_InstallBuildMessages, prc_InstallVersionControlMessage, etc. We need ALTER permission to do this.
    • EXECUTE – We need this permission to execute prc_Install*Messages stored procedures.
    • CREATE DATABASE - We need this permission to be able to create configuration, collection, and warehouse database.

The set of these permissions is very close to CONTROL permission. Unfortunately, instead of going granular we simply require CONTROL permission.

Brian