The object owner frequently is the user DBO, so many people think that the second part of the three-part name must be the database owner. But that would be pointless—because the database owner always has the username DBO, using DBO in that position of the name qualification wouldn't give any extra information. The second part of a three-part name is the object owner, who might or might not be the user DBO. The object owner might be the user sue, in which case any user but sue would always have to specify the object owner name sue.
In SQL Server 7.0, Microsoft introduced the ability to create a linked server, through which you can access objects on another SQL Server or other data source. Linked server objects have four-part names, with the first part being the server name. Because linked servers don't have to be SQL Servers, the documentation describes the third part of a four-part name as the schema name, even though SQL Server 2000 and 7.0 don't contain the concept of schemas. ANSI defines a schema as a collection of database objects that one user owns and that form a single namespace. A namespace is a set of objects that can't have duplicate names. Thus, two tables can have the same name only if they're in separate schemas, and no two tables in the same schema can have the same name.
SQL Server uses the object owner's name in this third position, but an owner isn't the same as a schema, at least in SQL Server 2000 and 7.0. Yukon will let you create and manipulate schemas, which will not only give greater ANSI compliance but will also solve some problems with object naming.
Because SQL Server 2000 and 7.0 treat schemas and users as the same thing, understanding the difference can be difficult. Every user has a default schema; if a user has permission to create objects, SQL Server puts all new objects that the user creates into that user's default schema (if the user didn't specify a schema). For example, when you create a user named sue, you're creating both a user and a schema. The default schema for the user sue is the schema sue. When the user sue creates a new table (assuming she has creation permission), the table goes into the schema sue, so other users must use the following syntax to access it:
SELECT <columns>
FROM sue.new_table
What's the Difference?
One way to start getting used to the difference between users and schemas is to realize that you can grant permissions to users, but you reference objects by the schema they're in. Every user has a default schema—which has the same name as the username—and in SQL Server 2000 and 7.0, you can't change this default schema.
A big feature of Yukon is user-schema separation. That is, creating a user won't automatically create a schema of the same name. In addition, although every user must have a default schema, Yukon doesn't require that the default schema have the same name as the username.
For backward compatibility, when you use the SQL Server 2000 or 7.0 stored procedures sp_adduser and sp_grantdbaccess, Yukon will create a user and a schema of the same name and make the new schema the default schema for the new user. However, Microsoft recommends that in Yukon you use the CREATE USER and CREATE SCHEMA commands separately so that you have full control over which schemas are created and which schema is the default for each user.
Yukon's CREATE USER command will let you specify a default schema for the new user. If you don't specify a schema, the default is the DBO schema, which every database always contains. Having the default schema be DBO means that when a user selects from an object without specifying a schema, SQL Server will assume that the object is part of the DBO schema. That behavior isn't much different from that of SQL Server 2000, in which SQL Server checks whether the user DBO owns the object if the current user doesn't own an object of the specified name. However, if a user with the default schema of DBO creates a new object, Yukon will place the new object in the DBO schema. This isn't the current behavior, but as I mentioned, it's an often-requested feature that will be especially handy for members of the db_owner role.
Looking Forward to Yukon
Next month, I'll give you more specific details about working with users and schemas in Yukon, and I'll show you the syntax for some of the new commands. In upcoming columns, I'll also tell you about more new security features in Yukon. And, as the Yukon release date approaches, I'll start giving more complete descriptions and internal details of the new security capabilities. Until then, this bit of forewarning about what to expect can help you be more aware of the behavior of and limitations in the current security model.
End of Article
Prev. page
1
[2]
next page -->
