SQL Server 2005 introduces the concept of schemas as opposed to object owners found in previous versions. This article will explain the differences between the two and, hopefully, clear up some of the confusion that still exists about schemas.
Object owners
To understand the difference between owners and schema, let’s spend some time reviewing object ownership. When an object is created in SQL Server 2000 or earlier, the object must have an owner. Most of the time, the owner is “dbo”, also known as the database owner. It is possible that an object can be owned by any user account in the database. The way to determine the owner is by looking at the fully qualified object name which you can see using SQL Server Enterprise Manager or Management Studio when you are viewing a list of the tables. For example, the name of a table called
orders owned by dbo is
dbo.orders. If the table’s ownership is transferred to user abc, the table will now be named
abc.orders.
How does an object get its owner? It depends on the user who created it. It is also possible for someone in the db_owner role to create an object owned by any user in the database. By default the user account that creates the object (the account must have CREATE TABLE permission) will also own the object. Only user accounts in the db_owner role can create objects owned by dbo. Even then, under certain circumstances, the owner will end up being the actual user account instead of dbo. See
Undestanding Object Ownership for an in depth discussion of this issue
Using dbo as the owner of all the database objects can simplify managing the objects. You will always have a dbo user in the database. Users in the database will be able to access any object owned by dbo without specifying the owner as long as the user has appropriate permission. 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 “ted” creates the sales table, it will be called
ted.sales. In order for users other than Ted to see the table, it must be referred to by the fully qualified name. If Ted 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 Ted’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 Ted’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.
Schemas
I like to think of schemas as containers to organize objects. If you take a look at the AdventureWorks sample database (Figure 1), you will see that the tables are organized by department or function, such as “HumanResources” or “Production”. This looks similar to the old owner concept, but has many advantages. First of all, since the objects are not tied to any user accounts, you do not have to worry about changing the owner of objects when an account is to be removed. Another advantage is that the schemas can be used to simplify managing permissions on tables and other objects. The schema has an owner, but the owner is not tied to the name. So, if an account owns a schema and the account must be removed from the database, the owner of the schema can be changed without breaking any code. If you do not wish to organize your database objects into schemas, the dbo schema is available.
Figure 1: AdventureWorks tables with schemas.
Let’s say that the employees within the Widgets department are members of the same network security group, WidgetEmp. The managers of each department are members of an additional group, WidgetManagers. We create a schema called Widgets and many tables, views and stored procedures are contained in the Widgets schema. To control access to the objects, we could add the WidgetEmp and WidgetManagers network groups to the SQL Server and to the database. Because we are concerned about controlling access to tables, the WidgetEmp group has been given execute permission to all stored procedures in the Widget schema. The WidgetManagers group has also been given select permission to all the tables and views. The great thing about this is that you no longer have to remember to grant permission whenever a new stored proc, table or view is created as long as it is in the Widgets schema.
To grant execute permission to all stored procedures within a schema, follow these steps:
- Using SQL Server Management Studio, expand Security then Schemas under the database.
- Right-click on the schema name and choose Properties.
- Select the permissions page and click Add to choose database users or roles.
- Once the users or roles are selected, a list of permissions will fill the bottom box.
- To grant execute permission to all stored procedures, check Grant next to the Execute item.
I have always wanted a database role that had execute permission on all stored procs. This would be similar to the db_datareader role. Now you can grant execute to all stored procs within a schema to achieve the desired result (see figure 2). I don’t know why a role like this doesn’t exist, but at least now there is a simple work-around. Even if you are not taking advantages of schemas in your database, you can give execute permission to stored procedures in the dbo schema to achieve the same result.
Figure 2: Grant execute permission on all stored procedures in the schema.
One important thing to keep in mind if you want to take advantage of schemas is that the schema organization must be considered early on in the design of the database. Changing the schema design late in the game could cause many changes to code.
Upgrading your database
What happens if you upgrade a database from SQL Server 2000 to 2005? When a database is upgraded from 2000 to 2005, a schema for each user in the database is created. You may not even notice this until you attempt to remove one of the user accounts. At that point you will receive the error message “The database principal owns a schema in the database, and cannot be removed”. To solve this problem just delete the schema first as long as it is empty. If the schema is not empty, you will have to decide whether to delete the objects first or transfer the schema to another owner.
Conclusion
While the concept of schemas is confusing at first, it has many advantages once you figure it out. Just think of the schema as a container to organize objects and simplify granting permissions as opposed to the earlier notion of owner. Most importantly, the schema organization must be considered early in the design process to avoid problems with code later on. Finally, by granting Execute permission in a schema to an account or database role, we now have a way to make sure that users can always execute new stored procedures.
source: http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server
