defaulting newly created objects to DBO

Hi, i know that for non-sysadmin role members, non-qualified objects
will be owned by the creating user.
Is it possible to change this so that objects created by users
belonging to database role 'db_owner' default to dbo?
Thanks,
RafetJust have the CREATE statement use dbo as the schema.
create table dbo.test
Randy Dyess
www.Database-Security.Info|||Not really. You could use sp_addalias but it's not
recommended to go this route and sp_addalias is provided for
backwards compatibility only.
Members of db_owner should qualify the objects they are
creating with dbo. It's considered good practice to always
qualify objects with the owner name - when creating or
referencing objects. Qualifying objects improves performance
and readability.
-Sue
On 5 Mar 2004 07:16:48 -0800, rducic@.hotmail.com (Rafet)
wrote:

>Hi, i know that for non-sysadmin role members, non-qualified objects
>will be owned by the creating user.
>Is it possible to change this so that objects created by users
>belonging to database role 'db_owner' default to dbo?
>Thanks,
>Rafet|||I am puzzled about this too. I am about to start using sp_addalias though.
I think Microsoft needs to think this area through a little better. I know
sp_addalias may go away some day, but the current version doesn't handle thi
s well.
In test, we like to give developers logins that link to the dbo user so that
we can have the system enforce all objects getting created by dbo. Then wh
en we create these in production we can be comfortable that the user is the
same and any code that refe
rences the user will always be "dbo" and not "joe_developer".
While it is best practice for developers to always reference user name with
objects, in practice it is harder to enforce without the system to do it for
us. Our company employs consultants regularly and they all seem to have di
fferent habits we end up ha
ving to work on with them.

Default User

What do I need to configure to make the sql tables and stored procedures I
create, be dbo owned, rather than owned by my id.
I can change the owner in each wizard, or create code, but I usually forget
and have to re do it.
PaulPaul
BOL says
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object.
"Paul" <nothanks@.btopenworld.com> wrote in message
news:7emdnYdtfvLVyqXbnZ2dnUVZ8v-dnZ2d@.bt.com...
> What do I need to configure to make the sql tables and stored procedures I
> create, be dbo owned, rather than owned by my id.
> I can change the owner in each wizard, or create code, but I usually
> forget and have to re do it.
> Paul
>|||Paul (nothanks@.btopenworld.com) writes:
> What do I need to configure to make the sql tables and stored procedures I
> create, be dbo owned, rather than owned by my id.
> I can change the owner in each wizard, or create code, but I usually
> forget and have to re do it.
Which version of SQL Server are you on? If you are on SQL 2000, the
only way out is to make yourself the database owner. On SQL 2005, you
can use ALTER USER to change your default schema.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns992546452A53Yazorman@.127.0.0.1...
> Paul (nothanks@.btopenworld.com) writes:
> Which version of SQL Server are you on? If you are on SQL 2000, the
> only way out is to make yourself the database owner. On SQL 2005, you
> can use ALTER USER to change your default schema.
>
I am using SQL2000, and I am a member of the db_owner role, but wizards and
not using the owner name defaults to my own id rather than dbo.|||BOL
I am using SQL2000, and con not see a sysadmin role to add myself too. I am
a member of the db_owner Role.
Paul
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23InYO1JjHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Paul
> BOL says
> The dbo is a user that has implied permissions to perform all activities
> in the database. Any member of the sysadmin fixed server role who uses a
> database is mapped to the special user inside each database called dbo.
> Also, any object created by any member of the sysadmin fixed server role
> belongs to dbo automatically.
> For example, if user Andrew is a member of the sysadmin fixed server role
> and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not
> as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
> server role but is a member only of the db_owner fixed database role and
> creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1.
> The table belongs to Andrew because he did not qualify the table as
> dbo.T1.
> The dbo user cannot be deleted and is always present in every database.
> Only objects created by members of the sysadmin fixed server role (or by
> the dbo user) belong to dbo. Objects created by any other user who is not
> also a member of the sysadmin fixed server role (including members of the
> db_owner fixed database role):
> a.. Belong to the user creating the object, not dbo.
>
> b.. Are qualified with the name of the user who created the object.
> "Paul" <nothanks@.btopenworld.com> wrote in message
> news:7emdnYdtfvLVyqXbnZ2dnUVZ8v-dnZ2d@.bt.com...
>|||Members of db_owner and db_ddladmin can create objects owned
by dbo but they have to qualify the objects when creating
them. On 2000, you can't change that behavior. Members of
sysadmins server role will have their objects default to the
dbo owner.
You have to be a member of sysadmins to add a login to
sysadmins.
-Sue
On Wed, 9 May 2007 13:58:38 +0100, "Paul"
<nothanks@.btopenworld.com> wrote:

>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns992546452A53Yazorman@.127.0.0.1...
>I am using SQL2000, and I am a member of the db_owner role, but wizards and
>not using the owner name defaults to my own id rather than dbo.
>