Re: db_owner role in SQL 2k

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 08/05/04 

Date: Wed, 4 Aug 2004 21:11:17 -0700

Hi Thomas

You need to make sure you understand the difference between logins, users
and groups. Please read the articles with these titles in Books Online
(Logins, Users, Groups)

The owner of a database is a login, which is listed in the sysdatabases
table in the master database as the owner of the database.

> The DBO is the Database owner - correct?

I try not to think of DBO as the Database Owner, but just as special,
powerful user in a database.

For anyone to use any database, their login name must have been given access
to that database and been given a USER NAME in that database. Sometimes the
user name and the login name are the same name, but they don't have to be.
For example, a login joe could have a user name joe in a particular
database.

To see your login name, run: SELECT suser_sname()
To se you user name in a database, run: USE <the database> SELECT
user_name()

The login who is the owner of a database automatically has access to that
database, and their user name will ALWAYS be DBO. Any login in the server
sysadmin role, including 'sa', will also have the user name DBO in all
databases.

The DBO user has special powers, including access to all objects in that
database, and the ability to grant permissions to others to access the db or
to create objects.

> So each database could have a different DBO?

Each user database could have a different login listed in the sysdatabases
table, so a different user would use the user name DBO in the database, in
addition to all sysadmins having the name DBO.

> If I am logged on as TT1, and I create a new database called travelData -
am
> I automatically the DBO?

If you create a database, you are listed in sysdatabases as the owner, so
when you use that database, your user name will be DBO.

> Now if joe1 creates a table pax in travelData - He has access to it and I
> have access to it (since I am the DBO) - Correct?

First, joe1 has to have been given access to the travelData database, and he
has to have been given permission to create a table there. Since the DBO
user gave him permission, the DBO user can access the table.

> greg2 does not have access to pax, unless he is specifically given
> permission - correct?

greg2 does not have access to the database either until it is given. Once
given, he still might not have access to the table.

> Or I could give greg2 db_owner permission on travelData, which gives him
> access to all the tables in this database - Correct?

After you have given greg2 access to the database, and given him a user
name, you could add him to the db_owner role, which would give him access to
all tables in the database.

> How does Public fit into all this?

Public is a ROLE, like db_owner, not a user. However, unlike the db_owner
role, members of the public role have no special permissions at all, except
what is explicitly granted. Also, unlike db_owner, all users are
automatically members of the public role. So if you do grant any permissions
to the public role, they apply to everyone.

There are more details and subtleties, but hopefully this information
answered your questions. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Thomas Scheiderich" <tfs@deltanet.com> wrote in message
news:10h33jmon8dn87c@corp.supernews.com...
> I have always been confused on the dbo issue.
>
> The DBO is the Database owner - correct?
>
> So each database could have a different DBO?
>
> If I am logged on as TT1, and I create a new database called travelData -
am
> I automatically the DBO?
>
> Now if joe1 creates a table pax in travelData - He has access to it and I
> have access to it (since I am the DBO) - Correct?
>
> greg2 does not have access to pax, unless he is specifically given
> permission - correct?
>
> Or I could give greg2 db_owner permission on travelData, which gives him
> access to all the tables in this database - Correct?
>
> How does Public fit into all this?
>
> I am just trying to get a better handle on this.
>
> Thanks,
>
> Tom
> "John" <anonymous@discussions.microsoft.com> wrote in message
> news:0f5d01c46ec6$890a7380$a601280a@phx.gbl...
> > Thanks for your help.
> > I will make sure that the owner name is included at the
> > time of object creation.
> >
> > Thanks a lot.
> >
> > -JC
> >
> > >-----Original Message-----
> > >Hi John
> > >
> > >Role membership doesn't affect the owner of an object.
> > The default owner of
> > >an object is the user who created it. Someone in the
> > db_owner role still has
> > >their own user name. However, a user in the db_owner
> > role does have the
> > >power to create objects owned by other users, including
> > owned by dbo. But
> > >the user has to explicitly list the owner name when
> > creating the object:
> > >
> > >CREATE TABLE dbo.NEW_TABLE
> > > ( .....)
> > >
> > >--
> > >HTH
> > >----------------
> > >Kalen Delaney
> > >SQL Server MVP
> > >www.SolidQualityLearning.com
> > >
> > >
> > >"John" <anonymous@discussions.microsoft.com> wrote in
> > message
> > >news:0b7901c46e83$9424b020$a601280a@phx.gbl...
> > >> I have added a new user in the db_owner role in SQL 2k.
> > >> When this user creates a table or any other DB object,
> > it
> > >> is owned by the user and not by dbo even though the ID
> > is
> > >> a part of DBO. Is this a default behavior in SQL 2k or
> > do
> > >> I need to do something different in order for the
> > object
> > >> to be owned by DBO.
> > >>
> > >> Thanks.
> > >>
> > >> John
> > >
> > >
> > >.
> > >
>
>

Relevant Pages

  • Re: Accidentally dropped DBO from database
    ... That error is due to having databases where the owner (dbo) ... is mapped to a login that doesn't exist on the server. ... a user in the database" when using sp_changedbowner, ...
    (microsoft.public.sqlserver.security)
  • Re: Confused about dbo
    ... Make sure you understand the difference between login names and user names. ... database within a SQL Server instance. ... There are several ways you could have the username dbo. ... the true owner of the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Login Name is missing
    ... Database ownership determines to login mapping for the 'dbo' user. ... > I see 'dbo' listed as the OWNER of all the tables. ...
    (microsoft.public.sqlserver.security)
  • Re: properties owner and users owner.
    ... 'dbo' is a special database user and must exist in every database. ... normally be the same login but can get out-of-sync in some situations, ... owner entries are synchronized: ...
    (microsoft.public.sqlserver.server)
  • Re: Who should I make the owner?
    ... Every login gets mapped to a username in every database they have access to. ... DBO is a special user name that exists in every database. ... NOT to think of DBO as meaning 'database owner' since many different logins ...
    (microsoft.public.windows.server.general)