Worst Practices - Objects Not Owned by DBO
-
I agree but.... purchased software sometimes dictates you must have a created ID as the owner. I just had one, create db called this, create and ID called this, use this ID to create the structures. BECAUSE THIS IS THE WAY THE PACKAGE WAS WRITTEN is the answer I get. It should not be changed right after that....
-
quote:
And while you're at it, unless you absolutely have to, do not turn on Case Sensitivity. With both, you will end up debugging things that used to work, and debugging things that you know should work.I live the case SenSitiVIty pain daily as I inherited to of our most critical production servers in that configuration. I now have to schedule the downtime, deal with the end users wrath and redo the configuration. Ackkkk.
Thanks for letting me know that I am not alone Mindy!!!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Andy,
No problem. Where should I post it? Here or in the scripts section?
I'll also have to clean it up a bit.
quote:
If you wouldnt mind, how about posting the script? Probably someone else may find use for it now that we're discussing it!Andy
Patrick Birch
Quand on parle du loup, on en voit la queue
-
Scripts section would be great, thanks Patrick.
Andy
-
Carl,
Thanks for comments. I think thats a good question - what is a good way to handle it? You could write some scripts to check for non-dbo objects occasionally. Aliasing is not a bad technique, though I agree that since MS doesn't recommend it it's apt to get broken at some point.
I have a simple technique - developers create stuff all day long on the dev server, send me the script. I run the scripts, which takes care of the dbo issue!
Andy
-
From a DBA standpoint I agree that requiring all objects owned by DBO is, by far, the simplest and most error-free method. However, how to do that appears to be easier said than done. As mentioned by another user (JAMYER) you cannot, as you implied in your last paragraph, solve the problem by allowing only members of db_owner role to create objects. Objects created by db_owner members still default to ownership by the user, not DBO. The creating user must be aliased to the "DBO" user in order for the default to be "DBO". And MS appears to frown on perpetuating the "obsolete" usage of aliasing.
Is there another way?
Alan Leiter
-
For now, aliasing is the only way to resolve it. I'd say this is the one case where aliasing makes sense, regardless of MS docs.
Andy
-
Not that I know of. Aliasing is the only way (other than changing the owner).
Steve Jones
-
How do you handle qualifying objects with dbo when using an application that can run on either Oracle or SQL Server (using ODBC)? Oracle has no 'dbo' user, so referencing objects via dbo.objecname won't work in ODBC code on Oracle (unless you like getting 'table or view does not exist' errors :). You could, of course, figure out which DBMS you are connected to and use something like: if (oracle) <reference one way> else if(SQLSERVER) <reference as dbo.<object>>, but that seems like a lot unnecessary branching. I suppose you could save the object owner someplace (say the registry), but if the object-owner should change on Oracle, then there are places outside of the database that would need to be updated. Not a pleasant prospect given that most of the users of the application don't even know what the registry is for, much less how to edit it.
Just curious how the more experienced would implement it. Seems like our developers have taken a lot of shortcuts in their programming. I think they may have violated every best practice and used every worst practice I've seen written up on this site...
Thanks all.
-
Hi Larry, thanks for the comments. You may want to post this in our regular forums that see more traffic. better chance of getting input from someone with cross platform experience.
As for your developers - they rarely do it maliciously. Most of the time its lack of education or lack of belief (they either dont know its bad or have been told that and DONT believe it) or the same two qualities in management!
Andy
-
I agree that having all objects owned by dbo is a good practice, but I have a question: what should I do if an application (used by the user joe) creates a table?
I'd like the name of the table to be "dbo.xxx" but SQL creates it as "joe.xxx"
-
You can change the name using sp_changeobjectowner, the only question is whether the app will break or not. If it just uses the tablename everything works since it tries currentuser.tablename first, then dbo.tablename. If it breaks you just have to roll back and live with it. In some cases the app may not know/care, just a function of what permissions the initial user had when the structures were created.
Andy
-
Steve,
Completely agree with your comment that "The only place where I have seen them useful is in allowing developers to create their own tables for testing." Had a hell of a time convincing an arrogant VB programmer from a major consultancy in the UK that this was the way to do things when you let developers have the freedom to do their own database design. Allows you to control the overall change process as they can *never* put anything into even the development DB which hasn't been thought out. Had to do it with stored procs too, as the general standard of SQL was like most developers, pretty dire when it went beyond anything containing an ORDER BY clause. Protects the other developers from the selfish idiots who either don't know, or in this case was just too lazy to bother learning, from just diving in, making a change, and messing it up for everyone else. If they have to sort out their own problems before the DBA will accept it for dbo ownership, it makes them think a little more, and only their productivity is majorly impacted. Otherwise, it's always the DBA who gets to clean up the mess in these cases.
As to Case Sensitivity...agree 100%, why make work for yourself?
Jon Reade
Edited by - jonreade on 05/30/2002 07:53:07 AM
Jon -
Comments posted to this topic are about the item Worst Practices - Objects Not Owned by DBO
-
BINGO!!!!
Francis
-----------------
SQLRanger.com
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply