Implications
The separation of ownership from schemas has important implications:
Ownership of schemas and schema-owned objects is transferable. This is accomplished using the ALTER AUTHORIZATION command.
Objects can be moved between schemas. This is accomplished using the ALTER SCHEMA command.
Beginning with SQL Server 2005, Microsoft introduced the concept of database schemas. A schema is now an independent entity- a container of objects distinct from the user who created those objects. Previously, the terms ‘user’ and ‘database object owner’ meant one and the same thing, but now the two are separate.
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 objects can be managed with greater precision than in earlier releases. This is accomplished using schema GRANT permissions object GRANT permissions.
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.
Object access and manipulation are now more complex as well as more secure since they involve an additional layer of security.
Advantages of using schemas
Apart from the obvious benefit that objects can now be manipulated independently of users, usage of schemas also offers the following advantages:
Managing logical entities in one physical database: Schemas provide the opportunity to simplify administration of security, backup and restore, and database management by allowing database objects, or entities, to be logically grouped together. This is especially advantageous in situations where those objects are often utilized as a unit by applications. For example, a hotel-management system may be broken down into the following logical entities or modules: Rooms, Bar/Restaurant, and Kitchen Supplies. These entities can be stored as three separate physical databases. Using schemas however, they can be combined as three logical entities in one physical database. This reduces the administrative complexity of managing three separate databases. Schemas help to manage the logical entities separately from one another, but still allow objects to work together where required.
Please Refer this link for Schema:<a href="http://msdn.microsoft.com/en-us/library/ms189462.aspx">http://msdn.microsoft.com/en-us/library/ms189462.aspx</a>[<a href="http://msdn.microsoft.com/en-us/library/ms189462.aspx" target="_blank" title="New Window">^</a>]
Submit an article or tip