Worst Practices - Objects Not Owned by DBO
-
I agree with you 100%. I am currently working on a project where they have made all objects owned by user "rms" and they have turned on Case Sensitivity, don't ask me why. It is like pulling your hair out. I think until people actually experience the agony they will not completely realize how unpleasant they can make life. I have learned the hard way more than once, and although previously I knew that making objects be owned by anyone other than dbo was bad, I had not yet lived with the consequences. Also, in this case, you can kiss all of your dynamic SQL (inside stored procedures) goodbye. From one who has been there, don't do it! 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.
Mindy Curnutt
-
Hi there
Yes, I agree and learnt the hard way. This is a real gotcha when your an oracle DBA moving into the SQLServer realm. Basically I now take the approach of all DBO owned objects then either application or other roles to security records accordingly, this is a much similar and better managed approach (and for all the reasons discussed in your article).
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Yes definitely
When I did the SQL 6.5 courses I was lucky enough to be taught by a trainer with commercial dba and they stressed the importance of this.
One thing I am not clear on. In SQL 6.5 you had to use sp_addalias to make logins impersonate dbo. SQL 7 seems to discourage this, and yet simply adding a login to the db_owner group seems to create these ownership issues.
How are you supposed to do it SQL 8 if you don't use sp_addalias?
-
I agree fully. Btw.: Have you ever tried to change owner on a userdefined data type? If it is a nightmare to change owners on ordinary objects, it has been impossible for me to figure out how to handle udt's.
Per-Johan
-
I agree for the most part...object ownership can be a real pain if everyone is allowed to create objects. There are some pretty strange circumstances where object ownership can be very beneficial. One interesting thing about SQL Server is that you can have a view owned by userA with the exact same name as a table owned by DBO. It's a very usefull way of limiting an applications access to a database without modifing the DATA or the application in any way. For example, an web application was written to allow HR to query employee records from a database before you were the DBA. Now you are faced with the task of removing the EMP_Password column from the web application, but the backend HR system still needs this data. You could just create a view of the dbo.Employee table With Encryption and name it dbo.Employee_View. Then create a View of the dbo.Employee_View view called WebAppUser.Employee and that's it you're done. Object ownership can be tricky, but object ownership can help you trick things too!
-
Dan, there are certainly some interesting things you can do with object ownership. Now whether you should...thats the question! If you're using it intentionally to solve a business need, good for you - though maybe sometime we can debate whether there is a better, more elegant method that would just use dbo:-)
Mindy - saw your post earlier, just didnt have time to respond - case sensitivity....definitely will make the worst practices list sooner or later! I'm ok with limited use of this on a few columns, but not database wide. What a time sink.
Thanks to all who have posted comments so far - I appreciate you taking the time to do so.
Andy
-
quote:
I am currently working on a project where they have made all objects owned by user "rms"WOW, I thought he only worked with free software. I wonder what the /. crowd will say.
Sorry, I couldn't help myself
-
I totally agree! I have been caught out a couple of times because of ownership - it's a real pain.
Life's much easier if it's dbo all the way...
-
This is somthing i have been trying to stress to the development team i work with for ages. thanks for a well written back-up to my nagging.
-
I agree with Andy overall. In ten years of working with SQL Server, ownership chains cause many more problems than they solve.
The only place where I have seen them useful is in allowing developers to create their own tables for testing. Since they are owned by the individual person, they are only for that developer to test. When the item progresses beyond the unit test stage, then they submit the table to the DBA, who changes the owner or recompiles.
Steve Jones
-
Andy,
I have been working with this problem this week. Amazing timing. We finished an application to a client a month ago, and she doesn't know SQL Server, but decided she could write reports to it anyway. She wrote some stored procedures and the other people in her office couldn't run them.
I diagnosed it as a ownership problem over the phone and was happy to hear I was right (it's so rare). I created and sent her a script that automatically changes all of her stored procedures to 'dbo'. Problem solved.
Patrick Birch
Edited by - pbirch on 10/17/2001 07:29:38 AM
Quand on parle du loup, on en voit la queue
-
If you wouldnt mind, how about posting the script? Probably someone else may find use for it now that we're discussing it!
Andy
-
Let me offer a slightly different point of view. I am mainly an end-user analyst and even though I have been caught up in the ownership issue many times, it can be helpful.
I am given read-write access to a single database, which I share with other people.
From a DBA point of view, the questions - who's table is this? who's hogging the space? are very easily answered with ownership tags - but can also be handled with naming conventions (but who adheres to that?).
For awhile, we analysts had everything as dbo, but this too is a nightmare. Import/Export defaults to a non-dbo name, as do other wizards. This made matters worse. Since everyone assumed dbo ownership, when it didn't work, it was tough to debug. In this environment, I had tables dropped and tons of work erased by others having full access to "my" tables with dbo ownership.
We eventually left the dbo world for the analyst database and the this-is-mine this-is-yours worked reasonably well since everyone knew that it existed and permissions had to be granted.
To me, the real question is, if this is truly a bad practice, then what steps should a DBA take to ensure that all wizards and gadgets conform to a universal ownership standard?
-
We strongly advise dbo ownership of all objects for our environment. From time to time there is a "special circumstance" where non-dbo ownership is a good thing. Those cases are few and far between, however. Another issue in this vain is being roled as DBO (via the EM GUI or sp_addrolemember) vs. sp_addalias. When you role a user's login (say developer12) to db_owner any object created by that user will be owned by developer12 instead of dbo. We therefore alias developers in dev environments. It works well, but as a production dba you need to remember to sp_dropalias when the db gets moved to production. Microsoft's view about this issue is made in Q234181--they say they prefer roles over aliases, but myself and many others disagree and prefer aliases. For now, given Microsoft's direction of the moment, we try to avoid aliases in production for SQL 7 and beyond.
-
quote:
We strongly advise dbo ownership of all objects for our environment. From time to time there is a "special circumstance" where non-dbo ownership is a good thing. Those cases are few and far between, however. Another issue in this vain is being roled as DBO (via the EM GUI or sp_addrolemember) vs. sp_addalias. When you role a user's login (say developer12) to db_owner any object created by that user will be owned by developer12 instead of dbo. We therefore alias developers in dev environments. It works well, but as a production dba you need to remember to sp_dropalias when the db gets moved to production. Microsoft's view about this issue is made in Q234181--they say they prefer roles over aliases, but myself and many others disagree and prefer aliases. For now, given Microsoft's direction of the moment, we try to avoid aliases in production for SQL 7 and beyond.
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply