Re: db_owner role in SQL 2k

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

Date: Thu, 5 Aug 2004 13:23:12 -0700

Try not to say "If sa is THE dbo", it's like saying "If I am the joe". It
might help to either say "If sa is the database owner" or, "If sa maps to
the username dbo"

SELECT suser_sname() shows you whatever name you logged in with. SELECT
user_name() shows you whatever name your login name maps to in the current
database.

DBO is user name, like joe or sue or greg, or thomas, but just one with more
privilege. The login 'sa' could be listed in sysdatabases as the database
owner, then when the login uses the database, his user name will be dbo. But
as I have said several times, any login in the sysadmin role will have the
same user name of dbo. Only one login can be the true database owner as
listed in sysdatabases, but many logins can use the user name dbo.

As far as showing permission, the tools only show the permissions that have
been GRANTed. No permissions are granted to sysadmins, they inherently have
FULL permission to do anything and everything. If you are sysadmin, SQL
Server just basically skips any permission checking code. So it might be
true that no one has been granted any permissions, but that doesn't stop any
sysadmins from getting in.

In your next reply you say

"I assume that my user_name() was dbo because I was a sysadmin at the time.
Now I am not, so it is the same as my login name."

This is true, but I'll make one clarification. If you are not in the
sysadmin role, and you are not the owner of the db as listed in
sysdatabases, your user name is not always the same as your login name.
When the user dbo adds a new user to the database, she can choose any user
name to map to the login name. Note in the syntax below, it is optional to
supply a specific user name in the db. If that is omitted, the user name
will be the same string as the login name. But login names and user names
are still two completely different things, even if they look the same. :-)

sp_grantdbaccess [@loginame =] 'login'
    [,[@name_in_db =] 'name_in_db' [OUTPUT]]

Arguments
[@loginame =] 'login'

Is the name of the login for the new security account in the current
database. Windows NT groups and users must be qualified with a Windows NT
domain name in the form Domain\User, for example LONDON\Joeb. The login
cannot already be aliased to an account in the database. login is sysname,
with no default.

[@name_in_db =] 'name_in_db' [OUTPUT]

Is the name for the account in the database. name_in_db is an OUTPUT
variable with a data type of sysname, and a default of NULL. If not
specified, login is used. If specified as an OUTPUT variable with a value of
NULL, @name_in_db is set to login. name_in_db must not already exist in the
current database. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Thomas Scheiderich" <tfs@deltanet.com> wrote in message
news:10h535l33osc77e@corp.supernews.com...
> "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: 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: 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)
  • 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: 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)