Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««45678»»

Worst Practices - Objects Not Owned by DBO Expand / Collapse
Posted Monday, November 14, 2005 12:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 22, 2006 2:23 PM
Points: 254, Visits: 1

This does not make sense.  There is no schema named dbo that I can find in SS 2005.  Each database contains a schema, and I guess you could name a schema dbo; although I would not be surprised if it was a reserved word.

Care to explain yourself a bit more?


david russell

Post #236950
Posted Monday, November 14, 2005 12:26 PM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:52 PM
Points: 290, Visits: 52
Agree wholeheartedly, but many of our 3rd party vendors use owners other than dbo. Causing plenty of expected confusion. Same sort of vendors who insist on using the sa login.

John Scarborough
Post #236952
Posted Monday, November 14, 2005 1:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 25, 2009 6:14 AM
Points: 876, Visits: 78
I really enjoyed this article and have shared it with my development team. The article brings to light some serious thought about ownership, which can be extremely important in the SQL world. That being said....

My 2 cents ....

Only production elements are owned by dbo. period! Development elements are owned by the developer working on the project. No one is allowed permission to grant dbo ownership except me which means nothing gets to production until it passes my inspection. I've only been working this way for a couple of years now and you wouldn't believe how much time this practice has saved me. I can now identify an offending developer in an instant, and, knowing this, developers are far more likely to have dotted their "I"s and crossed their "T"s before submitting the project for production.

Case sensitivity is always enabled. Maybe I've just been doing this too long and on too many different platforms, but I firmly believe that any project should - as best as is possible - meet the requirements of any platform that it may be ported to. I cringe at the thought of someone disabling case sensitivity on my SQL servers then having a customer come to me and say "That's exactly what I want to spend my $250,000 on, will it run on my existing ORACLE installation?" Not being Bill Gates, I'm gonna be pretty upset if I have to rewrite a bunch of sloppy code to make it happen.

Artificial Intelligence stands no chance against Natural Stupidity.
Post #236974
Posted Tuesday, November 15, 2005 3:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2008 9:31 AM
Points: 1, Visits: 3
What about the following scenario: There is a custom reporting app that allows the users to create report output as a table. This is a business requirement. You may not want the users to be able to run around the system with db_owner permissions but they still need to be able to create tables. While it can be confusing, since the names of the tables could overlap, the separate object ownership allows multiple users to run the report without stomping all over one another or giving away the keys to the kingdom security-wise. Thjs is locked down to a group level. Any thoughts?
Post #237348
Posted Wednesday, March 8, 2006 6:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 30, 2006 4:44 PM
Points: 4, Visits: 1

 All examples here are talking about a production databases and the importance of dbo owned objects.  Do these rules apply if we are developing on a development database/server.

Our new Oracle dBA's want to create seperately owned objects for all developers on the SQL Servers who are working on the same database. 


Post #264363
Posted Friday, May 9, 2008 8:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 15, 2015 12:42 PM
Points: 215, Visits: 430
I guess I'll have to post the dissenting opinion. While I agree that any system of solution can be made overly complex and difficult to deal with if you incorporate too many obscure features and techniques, nothing replaces good, solid documentation and best demonstrated practices.

Rather, I would approach the problem differently:

- Why is multiple ownership being considered? Is it "just because" or if there a valid, strategic reason for the approach?
- Are the associated technologies truly taking advantage of multiple ownership, or are there further work arounds in place? You want to make sure, that the strategy yields multiple dividends, and are not case specific.
- How "good" is the documentation and how well is the solution (problem space) understood by the team. Anyone who would step right into code to debug this type of problem (without capturing a trace first), clearly needs to take a step back.
- I'd have to say that qualifying a table is mandatory, unless the design explicitly called for it to be unqualified. Excuses about readability, effort, bigger code (really?? Are we back on 64k machines??) are just noise. Most of us are being paid decent coin to build these systems, so let's do it right!

Now when can it be beneficial?

- Multiple ownership strategies is a pretty good way to implement row level security, particularly when there's clear, role based requirements. For example, often, I've used multiple "view sets", to constrain results, in particular in a reporting environment.

- This approach can be used when you're implementing overlapping customizations, again based on roles (or clients). In particular, it becomes easy to activate / deactivate a client simply by controlling the schema.

- In the past, I've happily supported hundreds of developers in a single database, letting them create their own tables for development and testing purposes (under their own account). Sure it's chaos. So what? They know the rules of the game and are funneled and constrained as they move into system test and certainly Quality Assurance. Keeps them happy and productive, and fosters some innovation. If it means I need to keep on my toes, while part of my job is to serve them as well!

Ok, to dial this back a little. Anyone who uses any feature or capability of the environment without clear justification and understanding needs to be drummed out of the business. That includes object ownership. If there's not a good reason to use schemas other than dbo, that dammit don't do it. And while we're at it, let's consider alternate approaches and additional issues like recoverability, auditability, and maintenance (yes, multiple versions of a table can be a pain if you have to update them). Developer won't do the documentation? Have a friendly chat with their boss (and escalate nicely). Draw up a "form" that identifies the risks and have the boss sign it. That'll get action.

If I have a choice between mySchema.myTable and dbo.myScheam_myTable, i'll take the first one, every time. But, if the same requirements could be satisfied by adding a simple column to the table (i.e. AccessRole), then wow, let's do the simpler approach. Really depends on the application, and what you're trying to achieve.

Post #497927
Posted Sunday, May 11, 2008 5:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 20, 2009 12:49 AM
Points: 2, Visits: 10

I am new as a sqlserver dba and I do not agree completelly with this idea.I know that it seems very simple to refer every table with the owner "dbo" but at the same time if you have different business areas data integrated in your database you will have difficulties to separate them once everybody is owned by "dbo".
The AdventureWorks sample database is a good example, it has owners such as HumanResources, Person, Production, etc that makes easy for anyone to understand the businness data.
One trick that I use in oracle is to "create public synonym" for each table with the same name as the table, it will help you to not allow you to create tables with the same name and to identify redundances in your database.
Post #498505
Posted Thursday, May 15, 2008 2:28 AM



Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
I agree totally. And, to be honest, I think the separation of user/schema makes it even more complicated in sql server 2005. So you could have users a, b & c, you could have schemas a, b & c, but user a might be attached to schema b and vice versa.

Not a pretty thought - because you know it's going to bite you on Friday at 4:59PM.

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #501098
Posted Monday, May 19, 2008 9:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 23, 2010 12:44 AM
Points: 63, Visits: 46
Gotta say I like using schemas for objects. The majority of objects remain dbo of course but some cases come in handy because we can group the objects and assign access to the schema rather than each object. We've been known to use the following schemas:

1. ETL - all objects (sprocs, functions and tables etc) which are ONLY used by SSIS for the ETL processes
2. DBA - all objects which are ONLY used by DBAs - generally sprocs/views/function but sometimes tables too

It just separates the application objects from those used for specific purposes.

It makes using .Net code generators a little easier and yes, they could just be prefixes but that would mess up our naming standard ie that every object is prefaced with the main entity. This way, if I list objects by name I know which entity it relates to regardless of it's function and if I sort by owner then I can clearly separate which objects are used by the app (ie dbo), SSIS (etl) and us (dba). And because the application specific objects are left as dbo there are none of the ownership chaining issues.

All round it works well.

Kristen Hodges
Post #503278
Posted Thursday, January 29, 2009 7:59 AM


Group: General Forum Members
Last Login: Wednesday, November 30, 2016 6:30 AM
Points: 198, Visits: 345
Hi Andy,

I would really like to see what are your views on this same subject with the advent of SQL 2005/2008, that create real schemas, and allow grants at schema level.

Post #645948
« Prev Topic | Next Topic »

Add to briefcase «««45678»»

Permissions Expand / Collapse