Re: db_owner role in SQL 2k

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

Date: Fri, 6 Aug 2004 12:18:14 -0700

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:#wV6UoyeEHA.3476@tk2msftngp13.phx.gbl...
> 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.

Why would you use a different User Name than the login name? For example I,
just deleted and recreated tfs using Thomas Scheiderch as the User Name.

Where is the user name used?

and where is the login name used (other than to login)?

I know that for a user in NT, you set up a user name and a Full name, but it
is the user name that you logon with. And it is the user name that shows up
in the SQL drop down when creating a user (which now becomes your login name
and user name if you want - as you said).

I know you can also create your own login name if you are using Sql
Authentication (not if you are using Windows Authentication, however).

Just trying to get this all straight.

Thanks,

Tom.
> 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 give
n.
> > > 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: code access security
    ... Error 1 CREATE ASSEMBLY for assembly 'GmsSqlClr' failed because assembly ... owner has EXTERNAL ACCESS ASSEMBLY permission and the database has the ... make sure the database owner is mapped to the correct login on ...
    (microsoft.public.dotnet.languages.csharp)
  • 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: db_owner role in SQL 2k
    ... > 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. ... and their user name will ALWAYS be DBO. ... > automatically members of the public role. ...
    (microsoft.public.sqlserver.programming)
Loading