I like to keep things simple because simplicity is easier to manage and less prone to error. When I’m faced with schema design decisions, I pick the selection with the least complexity that meets my objectives. Here are some of my thoughts regarding schema and ownership in SQL 2005.
Schema
A schema is basically a container that categorizes database objects and simplifies security administration. As a namespace, schemas logically organize objects without the need for special object naming rules. Different objects can have the same name as long as they exist in different schemas because the schema name is essentially an extension of the object name that will “uniqueify” the name within a database.
Categorizing objects by schema is particularly useful in complex databases with many objects. There is some subjectivity on exactly how one might draw schema boundaries but the basic concept is the same; group related objects into different schema to provide organization to an otherwise unwieldy schema. Classifying related objects by schema makes complex databases easier to understand and manage.
Schema also simplifies security administration because permissions can be granted en mass at the schema level. For example, I can grant EXECUTE permissions on all objects in a schema with a single statement like “GRANT EXECUTE ON SCHEMA::Sales TO SalesRole”. I can grant CONTROL permissions on a schema to allow privileged users to full control over a specific schema but not others in the same database.
Even with the option to use multiple schemas, I tend to use the built-in dbo schema. I do this because most of the applications I maintain were developed before SQL 2005 and all objects are already in the dbo schema. Some of those legacy systems could benefit from multiple schemas but I’ll continue to use dbo for those applications to be consistent until I need to add a group of new objects that are appropriate for a separate schema. The new SQL 2005 databases I’ve developed thus far have been fairly simple and haven’t warranted using multiple schemas for either classification or security purposes.
Ownership
The owner is important for two main reasons: 1) the owner has powerful CONTROL permissions over all owned objects and 2) the owner determines whether or not the ownership chain is broken between objects. Schema-contained objects will inherit the schema owner unless explicitly overridden using ALTER AUTHORIZATION. Personally, I think it best for objects to inherit the schema owner in the vast majority of cases; if an object warrants a different owner than the schema, the object probably belongs in a different schema.
I use the built-in dbo principal for ownership unless I have a reason to do otherwise. This approach is perfect in environments where only db-owner role members can create objects and schemas are used solely as a namespace rather than a security boundary. The dbo principal exists in all databases so there is no a need to create a user or role for ownership purposes. Simple is good.
Different schema owners provide a security boundary between objects in different schema because this breaks the ownership chain. With a broken chain, explicit permissions on indirectly referenced objects are needed by the end user or the impersonated principal. Different schema owners ensure that I don’t inadvertently provide access to data in different schema via ownership chaining.
Note that an owner can be any database principal and does not necessarily need to be associated with a login. I find this feature especially handy in situations where I want to specify an owner other than dbo. Since I can specify an owner that is not a real person, I won’t need to change ownership if the owner leaves the company or moves on to other roles in the organization.
It’s probably best to create a principal (role or a user without login) with the same name as the schema for ownership purposes when different owners are desired. The only case I can think of where it might be appropriate for a real person to own a schema (at least initially) is in a development environment when non-dbo users might create schemas and objects.