What are some best practices for using schemas in SQL Server?
We use them
- to separate permissions per client (eg we have desktop, WebGUI etc schemas)
- for logical grouping eg (Data and Staging schemas for our tables).
Useful and practical observations past the white paper mentioned by Marian:
- GRANT on the schema: no more permissions per object. So a new proc in the WebGUI schema automatically has the permissions of the schema
- Nice groupings in SSMS Object Explorer
- OBJECT_SCHEMA_NAME
- You are forced to qualify object names (which is best practice)
I think that an answer can be found in this MSDN article: SQL Server Best Practices – Implementation of Database Object Schemas.
Quote: "This white paper discusses the opportunities for improvements in the security administration of a user database, and it outlines some best practices around using schemas to manage database objects in development and production databases. Specifically, it addresses three real-world scenarios:
- Protecting database objects from being altered by users without the knowledge of the database owner
- Preventing database base objects, independent software vendor (ISV) databases in particular, from ad hoc or incorrect user access leading to poor application performance
- Bringing related groups of objects (logical entities) together within one physical database to reduce physical database administrative overhead".
I especially use the third part - relate group of objects together based on their logical meaning - different schemas belonging to different projects inside the same database.