Managing changes to the database does not have much to do with your choice of version control systems, if you have it first. Of course, if you use change management tools from MS, you can be absolutely sure that they have been tested against TFS and the rest of the MS development kit. It’s true whether you use DBPro or older / crappier integration forms that can be seen in the classic VS database project or in SQL Management Studio, devoid of binding to projects / solutions. But there is no reason why you cannot use DBPro with Subversion or Red Gate with TFS.
The same goes for assembly. CC.NET vs Team Build, NAnt vs MSBuild, etc ... official MS tools, as a rule, roughly coincide with competitors. You have not described the database deployment process in great detail, but I can’t understand that in MSBuild it will be much more complicated than the script that you are using now, if at all. It is also easy to select different toolkits at different points in the stack: you can have MSBuild-based CC.NET builds that use the Red Gate command line deployment, or any other combination. It seems to me that the close integration offered when inserted into the MS world far exceeds the quirks in any tool, but there is a choice.
Let me understand: it seems your main problem is not technical, but getting database administrators to actually implement version control in the first place. If your dev and prod environments are their own living beings, not generic machines, determined solely by the result of some repetitive build process, then you are not using version control in your book. Imagine if a client-developer sometimes manually configured DLLs on different machines around the company, then complained that they were too difficult to synchronize; you would think that he is crazy.
In addition, the most important investments fall into a place where nothing is ever done directly from the database (more than you could pump in% programfiles%). If it is not in the source repository, it does not exist.
I do not think you are so important. You can write all your CREATE and ALTERs in Notepad, check them on the command line and create a “build process” as a 2-line shell script that combines them into a well-known file where the deployment script knows what to see. Or you can use a fancy tool like DBPro to enhance your performance with intellisense / unit / offline simulation testing, etc. There are excellent reasons for moving in the last direction (especially if you think declarative programming is what you should strive for in general), but I really think that the first step is the biggest.