How many of you grant access to schemas in your databases? I'm curious because I slowly am starting to see more and more people using schemas in their designs and not defaulting every object to "dbo". That was the default action for many developers for years, and it's still a habit of mine. Without SQL Prompt to add the schema to my queries, I'd still be producing code that followed that practice.

Schemas exist to allow some separation of different types of objects. In early versions of SQL Server, we didn't have schemas, but considered the third of four part naming to be the owner. In other RDBMSes, the use of a schema was to allow grouping and separation of different objects. This was also, and still is, a security boundary that helps ensure that authorized users can be easily limited to data access for certain objects.

This is also a nice organizational boundary for related objects that have the same schema, or even name. I have seen auditing or etl schemas that maintain the same named table that exists in a dbo schema, but is separated by placing this in a separate schema. I have even seen some archiving schemas that move data to a related table that exists in a different schema, making it easy for new administrators and developers to find (or remove) older data.

This week, let us know if you manage objects and security with schemas or if you stick to granting access at a database/object level the two more common ways of working with databases. If you have never used schemas and stuck with the defaults, perhaps you want to learn a bit more about how schemas work and the ways in which they may make your work easier.