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.
Referenced by: http://www.sqlteam.com
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.
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.
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.
You can create schema and create a new table that is related to this schema as following:
CREATE SCHEMA Production
ALTER SCHEMA Production