T-SQL schemata to organize code

Question

Hi,

I have a ms sql server database with a growing number of stored procedures and user defined functions and I see some need to organize the code better. My idea was to split sps and functions over several schemata. The default schema would hold the the sps called from the outside. The API of the database in other words. A second schema would hold internal code, that should not be called from the outside. I would probably do the same for tables: Some contain "raw" data, some hold precalculated data for optimizations, ...

As I have never used schema, I have several questions:

• Does this make sense at all?
• Are there any implications that I'm not aware of? For example performance issues when a sp in Schema A is using a table in Schema X?
• Is it possible to restrict the "outer world" to use only sps in a certain schema? For example: User A is only allowed to call objects in schema A, but sps in schema A are still allowed to use tables in schema B?

As this question is somewhat subjective, I have marked it as "community wiki". Hope that is ok.

Answer 1

• yes, it makes sense

• no difference in performance if all schemas have the same owner (ownership chaining)

• yes, permission schemas explicitly per client or have some check internally

We uses schemas to separate data, internals SPs, internal functions, and then SPs per client.

One advantage is we GRANT permissions on the schema not on objects, which is what I personally needed to clarify in my question, before we started using them.