The person installing TFS needs to be a member of sysadmin fixed server role on the SQL Server.
You may not realize it, but if you’re a member of sysadmin on SQL Server, there is precious little you cannot do on the SQL Server. You are, essentially, a god. How did you get these permissions? If you’re using SQL Server Express, TFS setup gave them to you during installation. If you installed SQL Server manually, you most likely added yourself to this role as you were clicking through the installation wizard. Of course, the SQL Server installation wizard doesn’t tell you any of this. It asks something completely innocuous like “Add Current User?” But once you click that Add button, you have real power—at least on that SQL Server.
This is why many easy going DBAs balk at the mere thought of hosting a TFS database. I recently read an email where one DBA joked that he had to get out the vinegar and wire brush to clean up his SQL server after a TFS install. DBAs are sensitive like that.
Why does TFS need so much permissions? Good question. You can go here for an explanation. The bottom line, though, is that the person installing TFS needs to be a member of the sysadmin fixed server role on the SQL Server. If you installed SQL Server yourself, you most likely have these permissions. If you have to ask a DBA in your organization for them, be prepared to convince that DBA that TFS means the SQL Server no harm.
It’s just the only way to make it work.