SQL SERVER – Importance of Database Schemas in SQL Server

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.

This concept of separation of ‘user’ and ‘object owner’ may be a bit puzzling the first time one encounters it. Perhaps an example may better illustrate the concept: In SQL Server 2000, a schema was owned by, and was inextricably linked to, only one database principal (a principal is any entity or object that has access to SQL Server resources, for example a user, role or a group). This meant that if, say, a user creates a table in the database, that user cannot be deleted without deleting the table or first transferring it to another user. But in SQL Server 2005 one can now simply create the table first and attach it to a schema, even without having created the user. This can be accomplished via the Transact-SQL statement below:

CREATE TABLE MySchema.MyTable (col1 int, col2 int)

Note that here ‘MySchema’ refers to the schema that owns ‘MyTable’, as contrasted to SQL Server 2000 in which for the same statement, ‘MySchema’ would have referred to the user who owns the table. This separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user. A schema can only be owned by one user at a time, but a single user can simultaneously own many schemas.

Default Schema

Because objects are no longer tied to the user creating them, users can now be defined with a default schema. The default schema is the first schema that is searched when resolving unqualified object names.

The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema. It is important note that if the user is authenticated by SQL Server via the Windows operating system, no default schema will be associated with the user. Therefore if the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema, though not directly with the user.

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.
• 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.

Object protection: Through schemas, a DBA can control access to crucial objects that would otherwise be open to potentially destructive changes by the users.

Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such as Siebel and SAP, they are tuned for specific application access paths by using many customized indexes. Ad hoc access or alteration to the underlying base tables of these applications can severely impact performance of queries and the application itself. Using schemas, a developer can logically group objects and even create custom objects without running the risk of messing up the underlying ISV database.

Conclusion

Schemas, introduced in SQL Server 2005, offer a convenient way to separate database users from database object owners. They give DBA’s the ability to protect sensitive objects in the database, and also to group logical entities together.

Reference : Pinal Dave (https://blog.sqlauthority.com),

http://msdn.microsoft.com/en-us/library/dd283095.aspx, http://msdn.microsoft.com/en-us/library/ms190387.aspx