User-Schema Separation

User-Schema Separation

The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

New Behavior
The separation of ownership from schemas has important implications:

• Ownership of schemas and schema-scoped securables is transferable. For more information, see ALTER AUTHORIZATION (Transact-SQL).
• Objects can be moved between schemas. For more information, see ALTER SCHEMA (Transact-SQL).
• A single schema can contain objects owned by multiple database users.
• Multiple database users can share a single default schema.
• Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases. For more information, see GRANT Schema Permissions (Transact-SQL) and GRANT Object Permissions (Transact-SQL).
• A schema can be owned by any database principal. This includes roles and application roles.
• A database user can be dropped without dropping objects in a corresponding schema.
• Code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users.
• Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.