I've been in a project until recently where we had to develop a new database interacting with several applications.

At the beginning it was just me who advocated against the plan "just do it like we did a similar db project 10 years ago" (started with SQL7 and then moved to SS2k; the code has been modified just minimally to reflect the deprecated features like ORDER BY in views).

So I suggested several rules:

Each app will get it's own schema where all the app-related code will be placed.

SELECTS from an app will only be against views.

INSERT/UPDATE/DELETE will only be allowed through stored procedures.

Permissions are based on schema level (grant exec and select),....

The decision was to "give it a try".

During the project several adjustments needed to be made:

Examples:

Tables had to be separated (split vertically) to improve performance and reduce blocking -> the app Dev didn't even notice we had to do it nor when we did it

Requests for new stored procedures/SELECT's: the app Dev would request a new function and we would create the "interface" (name, parameter list, output format) and create the sproc/view with dummy data matching the expected output. Now the app dev and we could work in parallel: we made the code to return data based on "real data" while the app dev could work on their side in parallel. Did we have to bother regarding security? Not really, since all we had to do is add the sproc/view to the related schema and everything simply did work. This allowed us to reduce the time-to-deliver significantly.

When we had performance issues we could very easily identify the source being either the database or the app and we could start to solve such issues immediately.

But the major change was the ability to quickly answer the question: "Who needs access to data stored in coly c1 of table t1?" We've been able to answer it instantly (since we didn't add a view/sproc to a schema without a proper request and documentation and provided only the columns needed). It's been slightly more difficult for the folks dealing with the 10yr old project...

Guess what concept will be used for the next project coming down the road...