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 ««12345»»»

Worst Practices - Objects Not Owned by DBO Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2001 10:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:11 AM
Points: 31,168, Visits: 15,611
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
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #22435
Posted Wednesday, October 17, 2001 7:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 24, 2008 11:15 AM
Points: 575, Visits: 35
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
Post #22436
Posted Wednesday, October 17, 2001 7:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, October 14, 2014 7:15 AM
Points: 6,785, Visits: 1,901
If you wouldnt mind, how about posting the script? Probably someone else may find use for it now that we're discussing it!

Andy



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22437
Posted Wednesday, October 17, 2001 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2008 3:49 PM
Points: 3, Visits: 2

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?




Post #22438
Posted Wednesday, October 17, 2001 11:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 9:03 AM
Points: 171, Visits: 16
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.




Post #22439
Posted Wednesday, October 17, 2001 11:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 6:50 AM
Points: 1,289, Visits: 2,841
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.








Post #22440
Posted Wednesday, October 17, 2001 11:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 6:50 AM
Points: 1,289, Visits: 2,841
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....




Post #22441
Posted Thursday, October 18, 2001 1:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:12 PM
Points: 2,049, Visits: 3,587
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #22442
Posted Thursday, October 18, 2001 1:59 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 24, 2008 11:15 AM
Points: 575, Visits: 35
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
Post #22443
Posted Thursday, October 18, 2001 3:21 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, October 14, 2014 7:15 AM
Points: 6,785, Visits: 1,901
Scripts section would be great, thanks Patrick.

Andy



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22444
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse