Databases Reference
In-Depth Information
The default schema of MyTestLoginUser is DBO, as shown in Figure
3-9
. The default schema of a user is the
schema that's used if none is specified in a T-SQL statement. To make it easier on developers, change the default
schema to MyReadOnlySchema, so it doesn't have to be specified in T-SQL statements.
Figure 3-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 manage 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
3-10
shows how you've switched the security model around for greater flexibility and control.
