The way we build databases and design security is rarely done ideally. It doesn't matter what your Utopian design is for schemas and objects; most of us make compromise as we deal with the pressures and diverse requirements thrust upon us in the real world. That's unfortunate, but it's also reality. I often see advice to use some best practice, like using only stored procedures and revoking all access to tables. I rarely see this in place, though I sometimes think that an investment in developer education would quickly erase any productivity losses from implementing stored procedures and probably reduce security issues.

I don't know we'll get away from stored procedures, but I wonder if we can get away from tables. I've seen a lot of design refactoring lately that requires changing schemas. The use of views to hide base tables would allow developers to easily make changes changes. Implementing vertical or horizontal partitions (or federations), additing of new columns, and even enhancing your application with RBAC (row based access control) or encryption would be easy.

This has me wondering if we should consider it a basic practice to not ever expose base tables and use views for all of our data access? Would it make sense to make a tCustomers table and a Customers view from the very beginning of development? We could grant rights to the view and use that for all queries. Perhaps SQL Server should include a dataViewReader role and dataViewWriter role that would allow quick assignment of rights to all views, and not all objects. That might make this easy to implement.

I know there can be times when this wouldn't work, and the proliferation of queries building on views built on views can be an issue. However, I also think it would make sense for data access through tables to be the exception, not the rule. Would this work in your environment? I bet it would for most of you.