When working with SQL Server, it is important to understand the concept of database schemas. Introduced in SQL Server 2005, schemas provide a way to separate database users from database object owners, offering several advantages and improved security.

What is a Schema?

A schema is an independent entity that acts as a container for database objects. In previous versions of SQL Server, the terms ‘user’ and ‘database object owner’ were synonymous. However, with the introduction of schemas, these two concepts are now separate.

For example, in SQL Server 2000, a schema was owned by a single database principal (such as a user, role, or group). This meant that if a user created a table, that user could not be deleted without deleting the table or transferring ownership to another user. However, in SQL Server 2005 and later versions, a table can be created and attached to a schema without creating the user first.

Here is an example of creating a table and attaching it to a schema:

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

In this example, ‘MySchema’ refers to the schema that owns ‘MyTable’, rather than the user who owns the table.

Advantages of Using Schemas

Using schemas offers several advantages:

Logical Entity Management

Schemas allow for the logical grouping of database objects. This simplifies administration of security, backup and restore, and database management. For example, in a hotel management system, logical entities like Rooms, Bar/Restaurant, and Kitchen Supplies can be stored as separate physical databases. However, using schemas, these entities can be combined as logical entities in one physical database, reducing administrative complexity.

Object Protection

Through schemas, a database administrator (DBA) can control access to crucial objects, protecting them from potentially destructive changes by users.

ISV Database Access

In custom Independent Software Vendor (ISV) applications, database schemas are often complex and tuned for specific application access paths. Using schemas, developers can logically group objects and create custom objects without impacting the underlying ISV database’s performance.

Managing Schemas

With schemas, ownership of schemas and schema-owned objects is transferable using the ALTER AUTHORIZATION command. Objects can also be moved between schemas using the ALTER SCHEMA command. Additionally, a single schema can contain objects owned by multiple database users, and multiple users can share a single default schema.

Permissions on schemas and schema-contained objects can be managed with greater precision than in earlier releases of SQL Server, using schema GRANT permissions and object GRANT permissions. Schemas can be owned by any database principal, including roles and application roles.

Conclusion

Schemas provide a convenient way to separate database users from database object owners in SQL Server. They offer improved security, logical entity management, and object protection. By understanding and utilizing schemas effectively, database administrators can enhance the overall management and security of their SQL Server databases.