Use of schema’s can significantly simplify the SQL Server design and help build system which is more maintainable and has a security model which is relatively easy to administer.

People who extensively worked with SQL Server 2000 or earlier version often get confused between object owners and schema. To get a better understanding of Schema, lets review how the object ownership works in SQL Server 2000 or earlier.
Any user who creates an objects is the owner of the object. Most of the time owner is “dbo” as it is the default database owner though it is possible for other users to own objects. You can check the fully qualified object name to determine the object owner. For example, if the table name is dbo.employees then employee table owned by dbo. If the table’s ownership is transferred to user xyz, the table will now be named xyz.employee.

Using dbo as the owner of all the database objects can simplify managing the objects as you will always have a dbo user in the database. Other users in the database will be able to access any object owned by dbo without specifying the owner as long as the user has required permissions. If an object is owned by an account other than dbo, the ownership must be transferred to another user if the original account is to be deleted. For example, if a non-dbo database user called “Praveen” creates an employee table, it will be called “praveen.employee”. In order for users other than Praveen to see the table, it must be referred to by the fully qualified name. If Praveen leaves the company or department and his account must be removed from the database, the ownership of the table must be transferred to another user account using the sp_changeobjectowner stored procedure before Praveen’s account can be removed.

If the table has been used in applications or referred to in any definitions such as stored procedures, changing the owner will now break all the code. If the dbo had owned the table from the start, there would have been no problem removing Praveen’s account. The code would not have to use the fully qualified name, though there is a slight performance gain in doing so and is considered a best practice.

What is a Schema?

If you understand any object oriented programming language like C++ or C#,  think of schema’s as “namespace” for SQL Server objects. Like Namespace, Schemas helps you to logically group SQL Server objects.

When you create any objects in SQL Server 2005/2008 they are allocated to a default schema which is “dbo” (database owner account) in most of the cases. However it is recommended to explicitly specify the schema name when creating objects. A fully qualified object name in SQL Server 2005 now includes four parts delimited with dots, like this:

[[[server.] [database].] [schema].] database-object]

As the syntax show, only the fourth element (database-object) is mandatory.

AdventureWorks sample database is a good example of how Schema’s can be used. You will see that the tables are organized by department or function such as “HumanResources” or “Production”. It may appear same as object owners in SQL 2000 but this approach has couple of significant advantages over SQL 2000.

Advantages of Schema’s over Object Owners

1. Objects are not attached to any specific user account. So if the user account needs to be deleted we don’t have to worry about changing the objects owners.
2. It simplifies managing permissions on Schema objects. If the schema owner’s account is to be removed from the database, the ownership of the schema can be transferred to other user without breaking any code.
3. Use of Schema’s with database roles can simplify managing security. I have tried to explain it in more detail later in this post.
4. Overall, maintenance of database become easier and I will recommend the use of schemas if you’re working with more than 20 tables.

Database role security

Closely related to the subject of schemas, is database roles. If you start by creating a set of database roles for your application then this can be used to simplify managing security.

Lets create two database roles, one for read-only users who just want to retrieve the data and one for another users who can perform CRUD operations.

CREATE ROLE [CRUDUser] AUTHORIZATION [dbo] 

CREATE ROLE [ReadOnlyUser] AUTHORIZATION [dbo]

Now consider having a number of tables on the schema which must be available to both roles, but read only for the AppReadOnly role.

GRANT SELECT, INSERT, UPDATE, DELETE,EXECUTE ON SCHEMA:: [Payroll] TO [CRUDUser] 

GRANT SELECT, EXECUTE ON SCHEMA:: [Payroll] TO [ReadOnlyUser]

Now consider having a Reports schema, which we want all users to be able to change data, because it is used for reporting.

GRANT SELECT, INSERT, UPDATE, DELETE,EXECUTE ON SCHEMA:: [Reports] TO [CRUDUser]

GRANT SELECT, INSERT, UPDATE, DELETE,EXECUTE ON SCHEMA:: [Reports] TO [ReadOnlyUser]