September 12, 2002 at 8:38 am
As Mr. Sopczak stated previously, many times a software vendor forces you to create a customized database user under which to create the other database objects. I suspect the reason is because: 1. That's the way Oracle works, and 2. The vendor wants his product to work in Oracle in order to increase his market share.
In Oracle, there doesn't seem to be a good alternative to letting someone other than 'sys' and 'system' (their versions of 'sa') enjoy schema-owning rights. Typically, SQL Server and Sybase have one server, many databases; Oracle can have one server, many instances, but usually has just one instance per server... and always only one database per instance. If a server or instance must support multiple applications, you are reduced to distinguishing one schema from another by the owner name.
I like the SQL Server architecture, but it does add that layer of nastiness about matching up 'users' with 'logins'. And I can't figure out why, when 'restoring' a database to a different server that the one from which it originated, I must sit down and map the users to the logins. If I can figure it out, how come the restore can't? Security is simpler in Oracle, where a login = a user = a "schema", but arguably not as flexible.
September 12, 2002 at 10:05 am
How to make sure that the objects created are owned by dbo:
1) What we do - You can specify the owner name as part of the create statement. (e.g. CREATE TABLE dbo.MyTable...) We have a script template for the various objects that uses the owner name in all references to the object. The template feature of SQL 2000 seems to be a very underutilized and very useful tool.
2) Heresy - You could write an insert/update trigger on the sysobjects table that checked the owner name and raised an error if the wrong owner was being applied.
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
September 12, 2002 at 11:15 am
Interesting idea. I just try to avoid letting developers create objects, saves me some headaches!
Andy
September 12, 2002 at 12:44 pm
I completely agree with Andy on having DBO as an owner for all DB objects, especially on Production. And I pretty much always enforce this rule in our shop. Except one time, we worked on a project that required user to be able to execute truncate command on a table. The table was permanent but was used as a holding/temporary table for a large number of records that were first inserted into this table, then updated, then dumped into another transactional table. Delete wasn't an option because it took way longer. User had to be able to truncate this table each time before starting a new batch. I was completely against granting this user DDL permissions on the database, therefore I made him this object's owner. Since this table is not accessed by other users, it worked out fine.
September 13, 2002 at 8:15 am
Its the nature of things that there is always some gray, few absolutes. Can you give me an example of how using object ownership helps..besides the great one from narine?
Andy
September 13, 2002 at 8:43 am
Very handy Andy,
Nice article and good replies. I have just come in this morning to an ecom site that had issues because the application user, who creates object from config files (i.e. beyond my control), does not dbo.table_name when it creates objects. The app however does not like username.table_name.
Is there anyway in SQL7, bar sp_addalias or sp_changeobjectowner post creation, of getting all object created as dbo objects?
I just want to make sure I am reading this situation correctly before messing with the server settings, does aliasing have any gotchas associated with it?
Thanks
September 13, 2002 at 10:01 am
> 2) Heresy - You could write an insert/update trigger on the sysobjects table that checked the owner name and raised an error if the wrong owner was being applied.
I don't think SQL Server will allow this, and, um, er, don't ask me how I know. 🙂
September 13, 2002 at 11:58 am
Hi there,
How do you turn on case-sensitivity in SQL Server 7.0?
Thanks,
Abhijit
September 13, 2002 at 12:17 pm
Its a server wide setting set when you install it.
Andy
January 5, 2004 at 9:13 am
Does a user have to be a System Administrator in order for their objects to automatically be DBO owner ?
May 10, 2004 at 8:56 am
-Does a user have to be a System Administrator in order for their objects to automatically be DBO owner ?
I think so. I have a programm that must be able to create tables in Oracle and in SQLServer. We used to create tables with the name <user>.<TABLE> but this seems to be a bad practice. Now I have introduced a new user in SQLServer which is System Administrator to create the tables. The tables are now owned by dbo. The application that modifies the data uses another user with only a db_datawriter-role.
I was also wondering how other people are handling this, because the need for a sysadmin user to create tables is also not a very nice idea to me. But I'm afraid to get problems when I have to use Windows Authentication and we do not have our tables created with the owner dbo. So: is there an easier solution?
I've heard about sp_alias and sp_changeownership but how does this work?
September 23, 2004 at 4:53 am
Another belated agreement but what was true in 2002 is true now.
The practice may have seeped over from Oracle where application objects are usually owned by a specific user (sys & system should only own system objects). Oracle, however, has synonyms to cope with addressing problems - create a public synonym and other users can omit the owner. prefix. Nothing like this exists in SQL Server so stick to dbo for object ownership.
September 29, 2004 at 2:43 pm
SQL 2005 (Yukon) now have a concept of schemas - . ie. objects not owned by dbo. It has also now a concept of synonyms. I believe schemas and synonyms are specified in SQL-99. Does this invalidate this WP?
I for one, like the concept of schemas - which is another way of object grouping. I believe the concept of ownership was added by MS or Sybase for a reason.
I don't necessarily agree that all objects should be owned by dbo.
November 4, 2004 at 6:34 am
A great article about a very important topic.
At my previous place of employment, we ran into issues when a user would create a table in code, but not specify the owner as "dbo". This creates such a headache for anyone else referencing the ojbect, and is just not a good practice.
November 10, 2004 at 1:08 pm
Oh man, this is automatic: all objects should be owned by dbo. I've been fortunate enough to have all my projects this way.
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply