Re: db_owner role in SQL 2k

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

Date: Thu, 5 Aug 2004 12:45:05 -0700

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:OYrb28qeEHA.724@TK2MSFTNGP10.phx.gbl...
> You said:
>
> >>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. <<
>
> Do you mean EM lists VDW as the owner of the database? DBO is just a user
> name.
>
> I presume then that you are logged in as tfs.
>

Right, I am logged on to my server (raptor) as tfs when I do the 2 selects
you mention. And I am logging on from another workstation.

When I go to EM from Raptor and drill down do VDW and users, I get this:

        Name Login ID Database Access
         dbo sa Permit
         SqlExec Permit
         tfs tfs Permit

If sa is the dbo, I was confused as to why suse_sname() giving me tfs ( I
assume that is because that is what I am logged in as) and user_name()
giving me dbo.

I am still a little confuse with logins and user name and am researching it
now.

> Read my paragraph right before your 'This is what is confusing' below. I
> said:
>
> >>
> Any login in the server sysadmin role, including 'sa', will also have the
> user name DBO in all
> databases. <<
>
> So my guess is that the login tfs is in the sysadmin role on the server,
so
> that login will show up with the dbo user name in all databases.

You're right. I had forgotten I had given myself all the pre-defined roles.
I also assumed that when I looked at VDW and the tables, that since I was
sysdadmin, it would show me as having permission to access them. But EM,
apparently, doesn't go that far. Since sa is the dbo - so would have
permission - and tfs - which is sysadmin - I would have assumed that EM
would show both as having permission. As a matter a fact, if you look at
that part of EM, you would assume that no one has access to the tables.

That was what was confusing. I understand it better now (at least a little
better).

Thanks,

Tom

> You can run sp_helpsrvrolemember 'sysadmin' to verify.
>
>
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> news:10h3faaddea6h42@corp.supernews.com...
> > "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: system tables security
    ... EM and look at the Database User Properties Permission for ... permissions for this user' the Owner dbo has Select, ... Insert, Update, Delete and DRI permission,, ... I checked the Server Properties Under Server behavior, ...
    (microsoft.public.sqlserver.security)
  • Re: db_owner role in SQL 2k
    ... I just took tfs out of all of the Server Roles and I could not access VDW at ... I assume that my user_namewas dbo because I was a sysadmin at the time. ...
    (microsoft.public.sqlserver.programming)
  • Re: DBO/Permissions questions
    ... In your other post, you said that when you logged in as tfs, you had the ... user name of dbo, but here you say the user name is tfs. ... sysadmin role, then he will have full permission to do anything even without ... > I am trying to understand this whole permissions thing. ...
    (microsoft.public.sqlserver.programming)
  • Re: Permission question - another one
    ... I took myself (tfs) out of all of the Server ... There is no user defined my Sql Server as "TRAVAC/tfs" (even though a couple ... current_user = dbo ... If I log on using Sql Authentication as tfs, ...
    (microsoft.public.sqlserver.programming)
  • Re: db_owner role in SQL 2k
    ... I do the select user_nameand get dbo ... Do you mean EM lists VDW as the owner of the database? ... I presume then that you are logged in as tfs. ... So my guess is that the login tfs is in the sysadmin role on the server, ...
    (microsoft.public.sqlserver.programming)