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.