Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Worst Practices - Objects Not Owned by DBO

By Andy Warren, (first published: 2002/09/12)

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.

Total article views: 37543 | Views in the last 30 days: 10
 
Related Articles
FORUM

SQL Server 2005 Best Practices - How Best To Change The Ownership Of Many Objects

What is the Best Practices approach within SQL 2005 to changing the ownership of multiple objects of...

ARTICLE

Understanding Object Ownership

In SQL Server 2005, object ownership will have a different meaning, but since most of us work with S...

FORUM

How to change object owner......

Change Object Owner

FORUM

Reassigning the ownership of db_owner

Neophyte DBA has a schema question about ownership

ARTICLE

Worst Practice - Not Qualifying Objects With The Owner

Regular columnist Chris Hedgate follow up on Andy's recent article about Worst Practices with one of...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones