TF30040 error when attaching a team project collection

Whilst doing some tidying on a multi-server TFS 2010 system I needed to move a team project collection from one Application Tier (AT) to another. Both ATs (which were not a load balanced pair) shared the same SQL server data tier (DT). It should have been easy using the TFS Administration Console.

I backed up the TPC database using SQL Management tools, the TPC was then detached without any issue from the first AT. I then backed up the SQL DB again in the detached state.

I then I tried to attach the TPC on the second AT. I entered the SQL DT instance name to get a list of TPCs available for attachment and I got the error

**TF30040: The database is not correctly configured.
**Contact your Team Foundation Server administrator.

So I went back to the original AT and tried a re-attach and got the same error message. Strange I had not changed accounts, the TFSSetup account in use had enough rights to detach the collection but not list one to be attached, strange!

A quick chat with the DBA found the problem, the TFSSetup account in use on both ATs had had its rights trimmed on the SQL server since the system was installed. As soon as it was granted admin rights to the SQL server all was fine with the listing TPCs available for attachment and the actually attaching the TPC on the new server.

Though I did not try it I suspect that as soon as I had the list of available TPCs in the TFS Administration Console I could have removed the extra SQL rights. The TFSService account would be doing the actual attachment, as it had done the detach, the TFSSetup account only need to be used to list the available TPCs.