MS SQL Server New Users - Problem with table owners (2)

Author

Problem with table owners (2)

david epsom dot com dot au

2006-04-05, 3:28 am

Is there any special permission that is required for 'user1' to
be able to create tables as 'dbo'?

Is there any particular advantage or disadvantage to having
tables owned by "db_admin" or "dbo"?

I think that up to now, we have been lucky, 'sa' has been the
login name for dbo, the only user with Create Table privileges.

But now we have a system where the admin is normally done by a
"db_admin" user, not "dbo". Is that common? Is there any
particular advantage to not having your db admin log in as dbo?

As a consequence, he has a mixed set of tables partly owned
by 'db_admin', and partly owned by 'dbo'. I don't know enough
to care who the tables are owned by, but he has two copies of
some tables, which is already causing problems.

(david)

Sue Hoegemeier

2006-04-05, 8:26 pm

A user needs to be a member of db_owner or db_ddladmin roles
(or sysadmin) to create a table owned by dbo. Members of
db_owner and db_ddladmin roles need to qualify the owner as
dbo.object when they create the objects to be owned by dbo.
In terms of the impact of different owners of objects, it
affects ownership chains. Having the different objects owned
by different users for no particular reason other than who
scripted the object will only lead to headaches. The object
creation scripts should include a qualified owner to avoid
these issues.
You can find more on some of these issues if you check the
topics in books online regarding ownership chains.

-Sue

On Wed, 5 Apr 2006 18:25:45 +1000, "david epsom dot com dot
au" < david@epsomdotcomdot
au> wrote:

>Is there any special permission that is required for 'user1' to
>be able to create tables as 'dbo'?
>
>Is there any particular advantage or disadvantage to having
>tables owned by "db_admin" or "dbo"?
>
>I think that up to now, we have been lucky, 'sa' has been the
>login name for dbo, the only user with Create Table privileges.
>
>But now we have a system where the admin is normally done by a
>"db_admin" user, not "dbo". Is that common? Is there any
>particular advantage to not having your db admin log in as dbo?
>
>As a consequence, he has a mixed set of tables partly owned
>by 'db_admin', and partly owned by 'dbo'. I don't know enough
>to care who the tables are owned by, but he has two copies of
>some tables, which is already causing problems.
>
>(david)
>

david epsom dot com dot au

2006-04-05, 8:26 pm

Thank you.

I've changed our update scripts to explicitly specify
'dbo.tblname' in the create table scripts. All of our
original tables are owned by dbo, so that makes a matching
set.

If the scripts fail, at least that will be much clearer
than having tables with the wrong owner.

I don't have the opportunity to change the script process
to allow for systems where the owner should be some-one
else, so I'll have to wait to see if that problem arises
in practice.

(david)

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:rnm832tee9mked1
r80hmvuhhbc0trr6q5v@
4ax.com...
>A user needs to be a member of db_owner or db_ddladmin roles
> (or sysadmin) to create a table owned by dbo. Members of
> db_owner and db_ddladmin roles need to qualify the owner as
> dbo.object when they create the objects to be owned by dbo.
> In terms of the impact of different owners of objects, it
> affects ownership chains. Having the different objects owned
> by different users for no particular reason other than who
> scripted the object will only lead to headaches. The object
> creation scripts should include a qualified owner to avoid
> these issues.
> You can find more on some of these issues if you check the
> topics in books online regarding ownership chains.
>
> -Sue
>
> On Wed, 5 Apr 2006 18:25:45 +1000, "david epsom dot com dot
> au" < david@epsomdotcomdot
au> wrote:
>
>

Sponsored Links