December 9, 2004 at 9:45 am
Issue with db_owner.
It may be my misunderstanding, but when a user (myuser) is marked 'db_owner' for a database along with read/write and that user creates a table - for example mytest_table it should be created as:
dbo.mytest_table .....and not myuser.mytest_table
if dbo. should be in front, which I believe it should, any ideas why it isn't creating the objects as such?
December 9, 2004 at 11:46 am
Sorry no. Unless the person is the dbo of the database not just marked as db_owner in the db it will use their username as the owner. This is assuming that they are only a db_owner in the database role and not also an System Admin. or other Service Fixed role that would allow creation of objects.
December 9, 2004 at 11:56 am
Thanks for the quick response. I grow tired of managing the username.object situation. Not all users are careful about placing 'dbo.' in front of objects. It makes user and object management cumbersome.
December 9, 2004 at 12:38 pm
This one caught me out a few times.
When I first switched from SQL 6.5 to 7/2000 I kept on using the sp_addalias procedure to get around this.
However, best practice is to qualify your objects with the owner, regardless of whether or not you are creating them or using them.
There is a hidden performance boost in doing so. If you don't specify an owner SQL will always look for an object owned by you first, and if it doesn't find one it defaults to using dbo.
I have used user owned objects for things like personal preferrences but to be honest I've found that having a field for the user sid is much simpler than dealing with the chain ownership.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply