Database Reference
In-Depth Information
Figure 4-9.
Schema owner of a login
To change the user's default schema, you need to execute this statement:
ALTER USER MyTestLoginUser WITH DEFAULT_SCHEMA = MyReadOnlySchema
Now that the user has MyReadOnlySchema as its default schema, it can see the objects owned by
that schema directly, without having to specify the object owner. However, you haven't set the access
rights yet. Let's grant
SELECT
rights to MyTestLoginUser:
GRANT SELECT ON SCHEMA :: MyReadOnlySchema TO MyTestLoginUser
The following statement works again for the MyTestLoginUser account:
SELECT * FROM UserProperties
Why did you go through all this trouble? Because creating your own schemas is a great way to
simplify access control by granting rights to schemas instead of objects directly. In a way, schemas can
be used as a group, like a Windows Group, on which rights are granted or denied.
Figure 4-10 shows how you've switched the security model around for greater flexibility and control.
Figure 4-10.
Moving to a security model through schemas
