Ownership chaining, which I described in "Crossing the Line: Ownership Chains" (February 2004, InstantDoc ID 40963), offers a limited way to specify what context a particular executable module should use when being executed. The context depends on the owner of the module and the identity of the user executing the module. Ill-considered use of cross-database ownership chaining can cause security holes in SQL Server 2000 and 7.0. Yukon, the next SQL Server release, will give you far more control over the execution context of an object such as a stored procedure or function. My intention in this column is not to fully detail Yukon's features; I only want you to be aware of some of the limitations in SQL Server 2000 and 7.0 so that you'll be better prepared to take advantage of the coming enhancements.
This month, I want to prepare you for a big change you'll notice in Yukon having to do with usernames and object ownership. To be ready for the change, you need to understand the difference between a user and a schema. If you read my column regularly, you might notice that this topic is slightly out of the range of SQL Server behaviors that I usually cover. It involves no clever code, no system-table access, and no undocumented trace flags or DBCC commands. However, I decided to cover this topic because it's crucial to understanding one of the biggest changes in object management in Yukon, and it might even clarify some behaviors in the current releases that new users frequently have trouble with.
Logins vs. Usernames, Round 2
Before discussing users, owners, and schemas, I want to elaborate a bit on one point I made in February when addressing the difference between login names and usernames. I wrote, "DBO is only a username; it maps to a login that is the database owner." In my 17 years of working with SQL Server, people have always been confused about the special username DBO. You can think of DBO as being an acronym for database owner, but I find that just thinking of it as a username—albeit a special, privileged username—can help avoid certain confusions.
A few years ago, I was hired to provide technical review and editing for a SQL Server certification exam simulation program. The practice test had several questions that started something like this: "Assume the database owner is DBO." On the one hand, this wording is completely incorrect because, unlike database objects, databases are owned by logins, not users, and DBO is a username. On the other hand, if the question writer meant that the username used by the database owner is DBO, you don't have to make this assumption because no other possibility exists. The owner of the database can use no other name, and DBO is always the username used by the database owner.
Since SQL Server 7.0 introduced database roles, people have also been confused about the difference between the DBO user and the db_owner database role. The DBO user is always a member of the db_owner role, but not every user in the role is the user DBO. In SQL Server, by default the owner of any object is the username of the user who created the object, so even if user sue is in the db_owner role, objects she creates are owned by user sue. However, when creating an object, users in the db_owner (or db_ddladmin) role can override this default and specify a different owner or can change existing object owners by using the sp_changeobjectowner stored procedure. People on the public newsgroups frequently ask how to set a different default owner for newly created objects, but in the currently available releases, it's impossible.
What's in a Name?
You can refer to most objects in SQL Server by a three-part name, where the first part is the database, the second part is the object owner, and the third is the object name. So a SELECT statement with a fully qualified object might look like this:
SELECT au_fname, au_lname
FROM pubs.dbo.authors
If you don't specify a database name, the default is the current database. If you don't specify an object owner, SQL Server has two possible defaults. First, SQL Server checks whether the current user owns an object of that name; if not, SQL Server checks whether the user DBO owns an object of that name.