Schema, Owner for objects in MS SQL

Question

By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema (I think ms sql 2000 calls it owner, while ms sql 2005 calls it schema)

The owner/schema is really a role or user in the database. I've always left the default of dbo, but I've recently seen some examples in microsoft training books where some of their tables & stored procedures had different owners/schemas. When is it beneficial to do this and why?

Answer 1

This article explains it well, including the changes from SQL Server 2000 to 2005.

Answer 2

Organization

In a dev environment, the production copy of the objects are dbo but developers can develop in their own schemas. Then code can reference the prod copy or their changes very simply. Using aliases can make this technique even more simple.

---

Also, a production database might support numerous systems or subsystems. You can use distinct schemas to keep those objects grouped.

Answer 3

In SQL 2000 the Schemas where equivalent to database users, in SQL 2005 each schema is a distinct namespace that exists independently of the database user who created it.

I use schemas when I need to make features or modules that will be maybe used later in other projects, so I will be able to isolate the database objects that are used by the module.

Answer 4

The use of schemas is exceptionally beneficial when you have security concerns.

If you have multiple applications that access the database, you might not want to give the Logistics department access to Human Resources records. So you put all of your Human Resources tables into an hr schema and only allow access to it for users in the hr role.

Six months down the road, Logistics now needs to know internal expense accounts so they can send all of these palettes of blue pens to the correct location people. You can then create a stored procedure that executes as a user that has permission to view the hr schema as well as the logistics schema. The Logistics users never need to know what's going on in HR and yet they still get their data.

You can also use schemas the way cfeduke has suggested and just use them to group things in the object browser. If you are doing this, just be careful because you might end up creating Person.Address and Company.Address when you really just need a single dbo.Address (I'm not knocking your example, cfeduke, just using it to illustrate that both address tables might be the same or they might be different and that YMMV).

Answer 5

I've used schemas in the past sort of like namespaces so you could have multiple entities named Address ([Person].[Address], [Company].[Address]). The advantage to this is visual organization in SQL Management Studio, you can get the same thing by putting everything under one schema and naming tables with a single identifier (i.e. [dbo].[PersonAddress]).

I've also used them for developer vs. developer development prior to running SQL Server Developer Edition on all our dev machines (back when we had a centralized development database earlier in my career).