Re: db_owner role in SQL 2k

From: Thomas Scheiderich (tfs_at_deltanet.com)
Date: 08/05/04 

Date: Wed, 4 Aug 2004 22:00:04 -0700

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:e2Z2MJqeEHA.1656@TK2MSFTNGP09.phx.gbl...
> 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)

OK, I'll do that.
>
> 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.

This is what is confusing.

I do the select suse_sname() and get tfs
I do the select user_name() and get dbo

But in Enterprise Manager - when I go to VDW and then users, it has sa as
the dbo.

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

It did. And I need to study this and play with it more, I am planning to
concentrate on this for the next couple of days to get it down.

Thanks,

Tom.

>
> --
> 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: Confused about dbo
    ... neither the explicit database owner or aliased to the dbo user. ... member of the db_owner role does not mean you are aliased to the dbo user. ... When you connect to SQL Server you do so using a login. ...
    (microsoft.public.sqlserver.security)
  • Re: db_owner role in SQL 2k
    ... Windows login name. ... login that is the owner of a database and members of the sysadmin role. ... >> the username dbo" ... >> FULL permission to do anything and everything. ...
    (microsoft.public.sqlserver.programming)
  • Re: db_owner role in SQL 2k
    ... The owner of a database is a login, which is listed in the sysdatabases ... I try not to think of DBO as the Database Owner, but just as special, ... For anyone to use any database, their login name must have been given access ...
    (microsoft.public.sqlserver.programming)
  • 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: Change dbo
    ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
    (microsoft.public.sqlserver.security)