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.
