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.
>

default table permissions

Hi there,
How can I give full default permissions to a few users whenever I
create a new table in a database in SQL2000? Thanks
Hi,
You can assign the users to the db_owner role in the database. This should
give them full permission on that database.
In case you want users to have full control of SQL Server, you can assign
the login to the serveradmin role.
Thank you.
Regards,
Karthik
"Tester" wrote:

> Hi there,
> How can I give full default permissions to a few users whenever I
> create a new table in a database in SQL2000? Thanks
>
|||I'm not sure exactly what you mean by 'full default permissions'. If you
want those users to have only SELECT, INSERT, UPDATE and DELETE for all
tables in the database, you can add the users to the db_datareader and
db_datawriter roles.
If you want those users to have SELECT, INSERT, UPDATE and DELETE on only
certain tables, then you'll need to grant those permissions individually. I
suggest you create a role and add the desired users to that role:
EXEC sp_addrole 'FullTableRole'
EXEC sp_addrolemember 'FullTableRole', User1'
EXEC sp_addrolemember 'FullTableRole', User2'
GO
After you create a new table:
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.MyNewTable TO FullTableRole
Hope this helps.
Dan Guzman
SQL Server MVP
"Tester" <calinguga@.netscape.net> wrote in message
news:1179436343.187305.31290@.w5g2000hsg.googlegrou ps.com...
> Hi there,
> How can I give full default permissions to a few users whenever I
> create a new table in a database in SQL2000? Thanks
>

default table permissions

Hi there,
How can I give full default permissions to a few users whenever I
create a new table in a database in SQL2000? ThanksHi,
You can assign the users to the db_owner role in the database. This should
give them full permission on that database.
In case you want users to have full control of SQL Server, you can assign
the login to the serveradmin role.
Thank you.
Regards,
Karthik
"Tester" wrote:

> Hi there,
> How can I give full default permissions to a few users whenever I
> create a new table in a database in SQL2000? Thanks
>|||I'm not sure exactly what you mean by 'full default permissions'. If you
want those users to have only SELECT, INSERT, UPDATE and DELETE for all
tables in the database, you can add the users to the db_datareader and
db_datawriter roles.
If you want those users to have SELECT, INSERT, UPDATE and DELETE on only
certain tables, then you'll need to grant those permissions individually. I
suggest you create a role and add the desired users to that role:
EXEC sp_addrole 'FullTableRole'
EXEC sp_addrolemember 'FullTableRole', User1'
EXEC sp_addrolemember 'FullTableRole', User2'
GO
After you create a new table:
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.MyNewTable TO FullTableRole
Hope this helps.
Dan Guzman
SQL Server MVP
"Tester" <calinguga@.netscape.net> wrote in message
news:1179436343.187305.31290@.w5g2000hsg.googlegroups.com...
> Hi there,
> How can I give full default permissions to a few users whenever I
> create a new table in a database in SQL2000? Thanks
>