Datasheet

Schema
Each database can contain one or more schemas. A schema is a namespace for database objects. All data
objects in a SQL Server 2005 database reside in a specific schema.
SQL Server 2005 implements the ANSI schema object. A database schema is a defined namespace in
which database objects exist. It is also a fully configurable security scope. In previous releases of SQL
Server, the namespace was defined by the owner of an object. In SQL Server 2005, the ownership of an
object is separated from an object’s namespace. An individual user may be granted ownership of a
schema, but the underlying objects belong to the schema. This adds greater flexibility and control to the
management and securing of database objects. Permissions can be granted to a schema, and those per-
missions will be inherited by all the objects defined in the schema.
Object Names
Every object in a SQL Server 2005 database is identified by a four-part, fully qualified name. This fully
qualified name takes the form of
server.database.schema.object. However, when referring to
objects, the fully qualified name can be abbreviated. By omitting the server name SQL Server will
assume the instance the connection is currently connected to. Likewise, omitting the database name will
cause SQL Server to assume the existing connection’s database context.
Omitting the schema name will cause SQL Server to assume the namespace of the logged-in user. This is
where some confusion can be created. Unless explicitly assigned, new users are assigned the default
schema of
dbo. (See Chapter 6 for user and login management information.) As a result, all references to
database objects not explicitly qualified will be resolved to the
dbo schema.
For example, the user Fred logs in to the server
AUGHTFIVE and his database context is set to
AdventureWorks. Because Fred was not assigned a user-defined schema, he exists in the default dbo
schema. Fred wants to retrieve the contents of the Contact table, so he executes the following query:
SELECT * FROM Contact;
Fred’s query will resolve to AUGHTFIVE.AdventureWorks.dbo.Contact. Unfortunately, that table
does not exist. The fully qualified name for the contact table is
AUGHT5.AdventureWorks.Person
.Contact
. In order for Fred’s query to work, one of two things will have to happen. The query will have
to be rewritten to reference the appropriate schema scope, like the following example:
SELECT * FROM Person.Contact
Or, Fred’s default schema can be changed to the Person schema so that his query will be properly
resolved with the following command:
USE AdventureWorks;
GO
ALTER USER Fred WITH DEFAULT_SCHEMA=Person;
GO
13
Introducing SQL Server 2005
04_047046 ch01.qxp 10/18/06 12:18 AM Page 13