November 14, 2005 at 12:14 pm
November 14, 2005 at 12:26 pm
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
MCDBA, MCSA
November 14, 2005 at 1:37 pm
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.
November 15, 2005 at 3:27 pm
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?
March 8, 2006 at 6:42 pm
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.
May 9, 2008 at 8:18 am
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.
May 11, 2008 at 5:50 pm
Hi,
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.
Cheers,
May 15, 2008 at 2:28 am
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[/url]
Why I wrote a sql query analyzer clone
May 19, 2008 at 9:58 pm
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
b. http://bi-tch.blogspot.com
January 29, 2009 at 7:59 am
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.
Thanks
Filipe
July 16, 2009 at 12:35 pm
No, you are missing the power of using schema. You should not put everything under dbo.
Jason
http://dbace.us
😛
December 15, 2009 at 9:09 am
I know this is an old article but I'm glad I found it. I agree 100% for homegrown stuff. Sure, if you have to follow a given naming convention to support a 3rd party app, your hands are pretty much tied. I also don't give a hoot about ANSI compliance because truly transparent code portability is a myth and not using the advanced extensions available in each "brand" of an RDBMS is like having a scientific calculator and only using the 4 basic functions.
--Jeff Moden
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply