Worst Practices - Objects Not Owned by DBO

Question

Worst Practices - Objects Not Owned by DBO

Viewing 15 posts - 46 through 60 (of 71 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

I agree with the article. However, if you purchase an app. alot of them insist that it MUST be done this way. I don't like it but have to live with it. We have quite a few software packages that install this way and I am stuck with this.

lakusha SSCommitted Points: 1857 More actions · Answer 2

I agree, but...playing devil's advocate

a) Ownership is a pain in SQL2000 because it's ownership, not schema. SQL2005 changes that.

b) When working with hundreds or thousands of tables, it's a real pleasure to classify them by "module"/schema

c) Even in SQL2000, if you want your query plans to be reused, you must have explicit object ownership (dbo.myobject not just myobject). So not being explicit is another worst practice example.

Having the same object name used with different owners (SQL2000) or schemas (SQL2005) can be really confusing. What I would like is having a way to tell SQL to raise a compile error when the object owner/schema is not defined.

PS: despite all I said, I imposed all dev in my shop to create dbo objects only for all the reasons mentionned in other posts. I do as I want, not them

regards,

Eric

Patrick-166697 SSC Rookie Points: 44 More actions · Answer 3

It looks like I am going to be only discenting opion here.

I pretty much disagree - the same reason that I disagree people should use userids like 'admin' or 'su'.

Ownership by dbo is no ownership at all. dbo is not a person, group, etc. It is a default SQL Server userid. Yes it is easy to use, and yes alot of people use it by default. But tables owned by dbo indicate that they are not really owned by anybody. Personally I prefer all of my tables to be owned by a real person or group. That forces more structured programming and forces every database and table to be owned. I consider using dbo being somewhat slothful. It is basically skipping security setup and letting the system use the defaults for you.

I still use dbo sometimes. But when I am trying to add structure to an organization I will try to make sure that everything is owned by some group or somebody.

lakusha SSCommitted Points: 1857 More actions · Answer 4

Patrick,

Currently, "dbo" is not a SQL userid, it is the "db owner". It's a security role, not a user. And this is not SQL-99 compliant. What we call "owner" in SQL2000 notation becomes the schema in SQL2005 and schemas can be owned.

Larry Ossler Newbie Points: 7 More actions · Answer 5

The PeopleSoft application I administer relies heavily on object ownership. For each table, each user has their own view, named the same. Database ownership is the principle building block of their row level security. I'm not sure how the other ERP's do it, but with PeopleSoft it is critical.

kwitzell SSCrazy Points: 2470 More actions · Answer 6

I agree 100%, I will be forwarding this tip to one of my co workers who encoutered the very problem the article speaks to!

smk-127973 SSC-Addicted Points: 434 More actions · Answer 7

I agree with ur suggestion.. let me tell you what we do in our project..

in dev environment we give dbo permission to all developers and in production, create permission is limited to the dba. all code migration from dev to prod is done by dba only. this way always the owner of any object is dbo. this is pretty cool if you have two separate environments for dev and prod..

--smk

Dinand Veldman SSC Enthusiast Points: 152 More actions · Answer 8

I fully agree with you. It's a nightmare debugging code when objects exists that use another owner than dbo.

When a database is developed in our company I create a database (with dev in the name) which can be used for development and make the developer part of the db_owner role.

As soon as the projects has to be rolled out I copy the database to a production database (using a different name) making sure all objects are dbo owned and I'm the only one in the db_owner role. All other user-rights assignments is done by the means of roles. Works perfect

Keith Edwards Default port Points: 1446 More actions · Answer 9

Along these same lines I had a vendor tell me to not do this the other day. Basically I have the GIS software from ESRI called ArcSDE installed with SQL 2000 as my backend. I have two users that are responsible for loading all the layers so I added them to the db_owner role so all the tables they create would be owned by dbo. When I was working with ESRI's tech support the other day on a problem the tech guy told me it was wrong to do that and I should take away the db_owner role from those two users. When I pointed out that I would end up with tables owned by those users he just said it isn't a good practice because it could cause problems with their software's underlying sql. Has anybody run across something similar or have any suggestions? thanks

Keith

Bryan Ivie Grasshopper Points: 16 More actions · Answer 10

Keith,

I've had vendors who didn't want users in a database as dbo... it might screw up their software....

Of course it would if one of those users changed the schema. Probably not the best practice of course. I don't think it's the worst practice necessarily. Creating dbo's all over the place with no backups would probably be a good worst case article.

Some vendors are picky. They don't want anyone in the database but they want a special account created for their software that's an SA on every SQL server in the place! Sometimes you have to tell the vendors how you're going to do it and work around them... or find another vendor.

Bryan

Jorge Cordero Ten Centuries Points: 1144 More actions · Answer 11

I agree. The second best thing is to use the same owner if dbo is not possible (some peculiar security reason) and stick to it.

Keep it simple if possible.

Jonathan Schafer SSC Enthusiast Points: 186 More actions · Answer 12

If you want a good example, here it is. Not having dbo. prepended to stored procedures when calling through ADO.NET, leads to placing a COMPILE lock on the stored procedure. We have a web farm which caches a lot of data when the system starts up. I noticed that when restarting the web servers, we would get blocking on the database. I tracked it down to COMPILE locks on the stored procedures that load the data cache.

After doing some research, I found that pre-pending dbo. to the name of the SP in the ADO.NET call eliminated the COMPILE lock. Making this change eliminated a lot of restart headaches and all the blocking.

Here are a couple of links that also discuss the issue.

http://www.microsoft.com/sql/techinfo/tips/development/ownerprefix.asp

http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp

Mandeep Singh Bhutani SSC Rookie Points: 47 More actions · Answer 13

Can anyone give 1 performance related issue if I use "test.tablename" instead of "dbo.tablename"

Is it just a good coding practices that should be followed or do we get some advantage of usin dbo as the object name?

Regards, Mandeep

Jags2001 SSCommitted Points: 1529 More actions · Answer 14

With SQL Server 2005, there should be no need to have dbo own any user table. It is now good practice to have a schema, rather than dbo. It is a ANSI-SQL/99 specification to have a schema. For developers who were scorned by guilt with this worst practice article, it was worth the wait.

It's time to scrap this worst article. When SQL Server 2005 becomes widely used in the community, please publish an article "Worst practice -- a schema named dbo"

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 15

You are right on the money on this one. We've had deveopers here to that and wonder why thier results are funky....