This question has come up quite a few times and this is my take on this. During installation and configuration of TFS you give the TFSSetup and TFSService account certain DBO privileges on the SQL databases. Typically these are the two major accounts involved though you might have other accounts such as TFSReports, TFSBuild, TFSTest etc, depending on whether you followed the install guide to its letter.
Now SQL DBAs often ask the following question: Is it ok to revoke DBO privileges to these accounts after the initial setup when everything has been done, and the TFS Server is now in normal usage mode?. They ask what are the explicit database privileges and why these are needed.
In the following post, I break down this into two sets of situations, and point out that the distinction between the two modes (setup versus normal operation) are not clearly demarcated as everyone would have wished.
Default Set of Privileges:
The following (let us call it the Defaults) are required during installation, configuration and as well as during any operation that will be considered as a maintenance/setup/hot-fix/patch/reconfiguration of TFS.
The above are required for doing any setup/configuration/reconfiguration or just about running most of the command line Administration tools of TFS. In my opinion, it is best to leave TFS with the above set of privileges. Now let us see normal usage scenario.
Day-to-Day Usage of TFS:
There is no official document (as of this writing) that officially differentiates a normal usage mode of TFS, from setup phase so that DBA could then go and revoke certain privileges, for the day-to-day operations. Also there is a good reason for NOT doing that. Doing so can easily put you in an unsupported state (if you raise a support incident with MS). After a proper TFS installation, 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).
Some of the day-to-day aspects of using TFS might involve making some changes to the Database which require DBO priviliges. That is why the TFSService needs to have this privilege. Now what are these:- (the following is not an exhaustive list)
Log on as a service
Farm Administrators group for any SharePoint Web applications that Team Foundation Server uses
TFSExecRole or both of the following for any databases that Team Foundation Server uses:
The distinction between “normal-usage” and “Default” in the deployment and real usage of TFS is really not something well defined, though normally there should be, and DBAs are usually concerned with this.
In a REAL normal usage scenario the typical operations will normally spill over and if you disallow DBO privileges to the TFSService account, the Best Practices Analyzer (BPA) tool will flag it as a failure. This violates one of our standard recommendations from the Health perspective of TFS.
As I said there is no official documentation that distinguishes from vanilla usage to TFS-setup/configuration. You should consider the above and the following set of operations, as falling somewhere between the pure setup stage and vanilla usage, and most of these will fail when you revoke DBO from TFSService. So I strongly suggest/recommend NOT revoking the DBO privilege from the TFSService account.
The above set of commands do certain operation on the database tables of TFS - as regards the specific set of Database changes these would do, they would collectively end up doing almost all of the operations listed in the “Default” set which I stated above.
My suggestion/recommendation would be to allow the Defaults, as in my experience that is the one that is least likely to cause pain later down the line in the course of running TFS infrastructure. You can always disable the TFSSetup account (or the one you used to run the TFS Setup) after the initial install and configuration of TFS. Whenever you need to do a servicing operation you can re-enable the TFSSetup account, do your servicing operation (like a hotfix or patch installation) and then once again disable the TFSSetup account.