I have worked on projects architected from both perspectives, and have come to the conclusion that:
1) Using SPs for CRUD is insane. If you do this, most schema changes require changing the SP interfaces...when this happens developers will choose not to change the schema because it is too much trouble. Instead in many cases they will choose to overload fields, make use of Table1.ExtraColumn1, Table1.ExtraColumn2, etc...refactoring a schema in any useful way is a huge PITA. Making refactoring harder makes refactoring not happen.
2) Using a normal language like Java or C# to do reporting is usually way too much coding and the result is extremely slow reports. This is one case where business logic will usually end up getting duplicated in the middle tier and data tier as reports will need to do calculations that the middle tier also has to do (like sales tax calculations for example).
3) The security argument in favor of using SPs for everything doesn't hold water. Any security setup that you have can be implemented with a proper application of table-based security with roles and views. This is only even necessary if you have to have rock-solid database security. 90% of the time you can get by with security implemented in the middle tier. If you are paranoid about this, just stick your middle tier behind a trusted authentication wall (a physical application server) which has access to the database.
4) Business logic is much easier to implement in a real OO language than in T-Sql, Pl-Sql, or whatever.
5) With a modern ORM, you can detect schema changes and get compile-time errors when a field is renamed, a table is renamed, field made not-nullable, etc...try doing this with SPs that can exist not only on one server, but may be spread all over the place using linked server connections. Bottom line: if you rely on SPs for business logic, changes are HARD and very error-prone.
6) Version control...TFS has some decent database version control capabilities, but TFS is very heavyweight and expensive. In general, keeping your code in actual code files saves a lot of time when you need to roll back a change.
7) Unit testing is much easier when your business logic is contained in an actual class where dependencies can be mocked. You cannot do this with SPs...the best you could do is some awkward test database configuration which only one developer can use at a time if this database is on a centralized server.
8) If you use SPs for your business logic, all the dependencies for this business logic had better exist in the database! Need to check the results of a web service to validate some input? Have fun with that using T-Sql. Want to validate input without putting it in the database first? Try doing this in an SP for the rule: An new order must have at least one OrderItem.
Like I said, I have seen both sides of this and can tell you with certainty that a standard business application is much more maintainable and flexible with business logic in a real middle tier. I have seen no good use for SPs with the exception of reporting and mass, complicated data manipulations.