Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Worst Practice - Not Qualifying Objects With The Owner


Worst Practice - Not Qualifying Objects With The Owner

Author
Message
slider
slider
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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?





jeb58
jeb58
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.





chrisleonard
chrisleonard
SSC Eights!
SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)

Group: General Forum Members
Points: 970 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





Chris Hedgate
Chris Hedgate
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1499 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/
Chris Hedgate
Chris Hedgate
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1499 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/
imhappy
imhappy
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.....


Denny Figuerres
Denny Figuerres
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 5

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.


SWELL
SWELL
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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 !!





YS Goh
YS Goh
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1

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


chrisleonard
chrisleonard
SSC Eights!
SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)

Group: General Forum Members
Points: 970 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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search