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.
Search WWH ::




Custom Search