Worst Practices - Objects Not Owned by DBO


Last week I posted an article

about something I call 'Worst Practices', or WP for short. These are concepts

that are on the other end of the spectrum from 'Best Practices' (BP), yet I see

them used all too often. My goal in this series is to bring up some of these

practices and discuss why they are bad ideas. If we can't always engage in best

practices due to time constraints and other practical matters, we can at least

try to avoid the worst mistakes!

This week I'd like to discuss object ownership. In my opinion, having an

object owned by ANYONE other than DBO is a worst practice! Now let's talk

about why.

For those of you new to SQL or maybe just not familiar with the concept,

every object (table, view, stored procedure, etc) in SQL has an owner. You get

to be an object owner by being a valid database user, having the permissions to

create an object, and then by actually creating an object. You can easily see

who owns an object using Enterprise Manager, there is a column that shows the

owner for each object. The bottom line - by using ownership you can have

multiple objects with the same name.

Even if you don't think about it, you're using ownership when you right a

select statement that refers to a table, like this:

select * from Categories

When that statement is executed, SQL first tries to execute the statement by

assuming that you own the object. 'You' being defined as how you're

connected. Let's say I'm current connected as user 'wp'. That means what SQL

tries to do is this:

select * from wp.Categories

If that object doesn't exist, it then tries to do this:

select * from dbo.Categories

Having to do that little bit of work to figure out the correct object takes a

bit of time. You could make the argument that you should always qualify objects

with an owner - but that would be a best practice and I'll leave it to others to

write about those for now!

You know and I know that we don't qualify all objects. It takes time, makes

the code bigger, maybe a bit less readable. But the number one reason? When we

wrote the code, everything was owned by dbo, what would we gain by adding dbo.

to every object name?

Fewer headaches.

Take the situation where you've allowed objects to be owned by someone other

than dbo. You've got multiple copies of various tables and stored procedures,

even two Categories tables, one owned by dbo, one owned by wp. Your development

team writes a small app that uses the Categories table...the one owned by dbo,

though they don't fully qualify the object. Works fine in testing. You install

the app for user WP, it runs, but WP reports that he only sees five categories,

but user BP across the hall sees eight? You can step through the code all day

and not find an error. BP and WP are using different tables, but it's hidden

from the developer!

Contrived? Sure. But lot's of variations of that problem are possible.

Let's move on to part two of why this idea is such a bad idea - ownership

chains. If you've take the SQL exams, you know MS likes to throw in a question

or two about ownership chains. Avoid them! Keep your permissions as simple and

as clear as you can. Again, for those of you who aren't familiar with ownership

chains, it's the process SQL has to go through to figure out if a user should be

allowed access to an object/data. As long as all objects are owned by the same

user, the process is simple. Once the ownership chain is "broken" SQL

is forced to do more checking. Books Online has a pretty good explanation,

search for "Ownership Chains".

The fact is that few developers or DBA's intentionally use object ownership,

it usually happens by mistake. You can prevent it from happening, either by not

giving users the permissions to create objects unless they are a member of the

db_owner role, or you can let them proceed and then use sp_changeobjectowner to

make dbo the owner before you deploy the code. Of the two, I believe the first

is the better solution.

Agree with me on this one? Or think I'm way off? Either way, let's discuss it

- post your comments by clicking the discussion tab below.


4.86 (7)




4.86 (7)