A three tier application splits it work up into distinct tiers (Here is an example):
1. Presentation Layer
2. Business Layer
3. Database layer
While this is harder initially to set up it does provides a range of deployment, security, development and maintenance benefits.
Just because the database layer uses stored procedures does not in any way invalidate this model.
For deployment, with a corrrect design it is possible to only require detailed configuration once on the server as opposed to every client.
For security there are more oppertunities for security in depth (you only need grant database access to the Business Layer or Database Layer not every user).
For development there are more incentives to reuse code since the layers provide a some decoupling. In a two tier application there is far more room for code duplication. The layers also provide a point for performance tuning.
For maintenance having distinct layers means that a patch can be better isolated.
If you don’t change the interface only a single changed component needs to be deployed.
Developer Rants 