This is a very good question.

Just recently we have hired a company to create intelligent KPI (key performance indicators) set for us. In addition to the NDA (non-disclosure agreement), there are still some pieces of data that need to remain confidential especially when the data is not useful to the vendor(s).

Therefore, to make the short story longer, I have created schemas and roles, removed some of the columns (after the creation of the views, like SSN). Thus, the programmer(s) in the new role and schema will not be able to see the table design, references, definitions, and various security objects. Of course you as an admin would be able to modify the settings to your needs.

The new role and schema will let them create the new objects and execute the DBO objects but not modify nor view.

Hope this helps.