Database Reference
In-Depth Information
Schemas
A
schema
is a container that holds database objects; schemas reside inside a database. Schemas are part
of the three-part naming convention of database objects; they're considered namespaces. Each object in
a schema must have a unique name.
By default, objects created are owned by the DBO schema. For example, the
CREATE TABLE
statement
showed previously for the UserProperties table uses DBO as the schema owner (
schema_id
is always 1 for
DBO). See Figure 4-6.
Right now, the new user MyTestLoginUser can't read from this table. Attempting to issue a
SELECT
statement against UserProperties returns a
SELECT permission denied
error. So, you have a choice: you
can either give that user account
SELECT
permission or create a schema for that user and assign the
SELECT
permission to the schema.
Figure 4-6.
Viewing an object's schema ownership
It's usually much easier to manage access rights through schemas instead of users directly. To do
this properly, you need to change the ownership of the UserProperties table to a new schema (other than
DBO) and then assign access rights to the schema.
To create a new schema, you must be connected to the desired user database where
MyTestLoginUser has been created. Then, run the following statement:
CREATE SCHEMA MyReadOnlySchema AUTHORIZATION DBO
At this point, a schema as been created; it's owned by DBO. You now need to change the ownership
of the UserProperties table to MyReadOnlySchema:
ALTER SCHEMA MyReadOnlySchema TRANSFER DBO.UserProperties
The table now belongs to the schema, as shown in Figure 4-7.