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.
