Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Worst Practice - Not Qualifying Objects With The Owner Expand / Collapse
Author
Message
Posted Wednesday, March 10, 2004 11:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 29, 2005 3:55 PM
Points: 28, Visits: 1

In regards to the context of stored procedure execution, this article contains a contradiction and some confusion.

It is my understanding that statements executed within a SP default to the permissions level of the owner of the SP. If a SP was created as dbo.whatever then all statements executed within that SP would be in the context of dbo, meaning that any objects not owner-qualified would be accessed with dbo context first. IE you will never get a dbo.procedure1 with unqualified object names accessing those objects as user1.object. It will always access them as dbo.object.

The article seems to reiterate exactly this: "objects that are not qualified with owner name and referenced in SELECT, INSERT, UPDATE and DELETE statements will default to the owner of the stored procedure". But then it later says something different when explaining that John.Orders would be truncated when John executes a SP owned by Jane. According to the above explanation it would always truncate Jane.Orders since the table name is not qualified in Jane's SP and Jane owns the SP.

So which way is it really?




Post #105384
Posted Wednesday, March 10, 2004 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 25, 2006 9:53 AM
Points: 8, Visits: 1

I had never really thought about this subject when I read the article and I didn't get that impression.  I may have mis-read the article (and I'm posting without re-reading it) but I IMHO the warning about cache misses was clear enough to not lead to a misunderstanding.




Post #105390
Posted Wednesday, March 10, 2004 1:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, January 4, 2014 11:05 PM
Points: 968, Visits: 67

Jeb58,

I agree that the author's intent appears to be to indicate that defaults assumed in object name resolution aren't going to get you a cache hit, but there are all kinds of changes you can make to a query string that will lose the cache hit.  In fact, if I change anything in the query string, I lose the cache hit.  The presence or absence of an explicitly-named owner is just one way to mess this up, and since applications generate code the same way, I can get very, very good cache reuse from an application that never explicitly qualifies the owner.  This is the point that I think is - potentially - easily lost in translation in this article.

But you have your nickel, and I've only got my two cents.  If you understand cache reuse well, that's great.  But I'm suggesting this could be misleading to a newbie.  I certainly have my hands full trying to get some people in my organization to understand object ownership at all! 

Cheers,
Chris




Post #105399
Posted Wednesday, March 10, 2004 2:54 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
Chris,

Your thoughts about my intent of the example are correct. I never even thought about the possibility to misinterpret it the way you fear 'newbies' might. But I do understand your point. The key line I think is this one:

"...SQL Server will not reuse the execution plan for statements specifying differently qualified object names"

I see now that I might have been able to write that in a more explicit (no pun intended) way. What I of course meant was something like "...even if the only difference between query A and query B is the qualification of owner names, when executing query B SQL Server will not reuse the execution plan of query A". Maybe I could have also explicitly (again []) stated that also the execution plan for query B will be reused if it is executed again, just as the one for query B. It would just have been an extra execution in the sample script so that would probably have been a good idea. But I must admit that I have no intention of writing articles intended for absolute beginners, I think it is more interesting and appealing to a larger audience to raise the level a bit. Therefore I hope that most readers will understand the part regarding defaults and how these will be used for cache reuse. Finally, the main intent of the article was not at all to point at the performance gains you can get by qualifying objects with owner name, but rather to show that there are several good reasons to do it and none other than laziness not to do it.

In any way, I do not see how this advice can be wrong for anyone, newbie or seasoned veteran. Yes, there are some arguments to be made regarding code reusabilty (as stated in earlier messages in this thread), but I think there are other ways to handle those situations. And no matter how much a worst practice, there are of course always some kind of exception to anything. But as I said in the article, the only reason (except for the above) to not qualify objects with owner names is laziness. Or if you turn it around, if you do not have a reason not to qualify them, why should you then not do it?




--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #105412
Posted Wednesday, March 10, 2004 3:20 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
slider,

It is both ways. If you create and test the scenario I describe you will see that it is John.Orders that is truncated. Normally SQL Server will default to the name of the creator of the procedure when resolving the object names (at run-time), but for these statements it will use the name of the user executing the procedure.




--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #105414
Posted Thursday, March 11, 2004 8:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 17, 2005 6:15 PM
Points: 7, Visits: 1

Hey, I am just a newbie.  I also start to use stored procedures and I can't find out what actually cons and pros of using qualified object name except of performance.   It's not my laziness on typing 'dbo.'.  Just my boss asked me any cons and pros of NOT using qualified object name. 

In my point of view, with qualified object name can let me know what the object is actually used, but I need to amend code before releasing to production environment. A newbie is just a newbie.....

Post #105726
Posted Thursday, March 10, 2005 2:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 24, 2009 8:49 AM
Points: 14, Visits: 3

I see this article has a lot of folks with many of the same comments....

 

as one person pointed out the !!WORST!!  thing is when some bozo codes with servername and or dbname when you try to setup a new database you need them to not be hardcoded.

 

as for the owner prefix I will echo the question: 

WHY ?

in my experience only one set of tables is allowed in a database and if any other user or admin creates a table with the same name as a production database table they would be in deep hot smelly stuff with me! 

can anyone provide a compelling case for having multiple database objects with the same base name and different owner names?

I can think of possibly one case and very minimal at best:  different view and sp versions based on login user id.

other than that I see the use of more than one object with the same base name and a different owner name as a possible way to confuse users and lead to database problems.

 

Post #166787
Posted Thursday, November 17, 2005 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2007 9:40 AM
Points: 6, Visits: 1

Excellent article but I note the author doesn't follow his own advice in the example code in some of his other articles eg SQL Injection etc !!




Post #237854
Posted Thursday, December 1, 2005 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 23, 2006 6:53 PM
Points: 4, Visits: 1

Instead of user id, can we use the user-defined role id as the table owner? any impact?

 

 

Post #241038
Posted Thursday, December 1, 2005 7:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, January 4, 2014 11:05 PM
Points: 968, Visits: 67
Yes, a role can own objects just like a user. This is actually an interesting option. The key to this thread is to specify the owner as part of the object name, whether that is a user or role.

Cheers,
Chris



Post #241112
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse