Confused about dbo

I recently signed up with a web hosting company. The hosting package
included a Microsoft SQL server database with it.
Anyway here is my question. I know for sure that I am not the owner of the
database (dbo) because hosting companies don't allow their customer to
create their own databases, so why is it that when I create a table the
owner of the table shows up as dbo? I would think that the owner should be
my user name right?
Why is this happening?
Thanks for your help.What does running the following in your database return
select current_user
It sounds like you are the database owner (your login has been mapped to the
dbo user in the database) since if you were simply a member of the db_owner
role you would indeed have to prefic objects with dbo when creating them.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"msnews.microsoft.com" <Rene> wrote in message
news:eDSuZ9oqFHA.3540@.TK2MSFTNGP14.phx.gbl...
>I recently signed up with a web hosting company. The hosting package
>included a Microsoft SQL server database with it.
> Anyway here is my question. I know for sure that I am not the owner of the
> database (dbo) because hosting companies don't allow their customer to
> create their own databases, so why is it that when I create a table the
> owner of the table shows up as dbo? I would think that the owner should be
> my user name right?
> Why is this happening?
> Thanks for your help.
>|||Hey Japer:
Well, I am away from the computer that has access to the database so I can't
run the "select current_user" function right now, however, isn't me being
the dbo a bad thing from the point of view of the hosting company?
Wouldn't I be able to do things such as increase my database space or screw
some other things up by me being a dbo? Especially since my database is on a
shared SQL server?
Thanks
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23VFQqKpqFHA.3540@.TK2MSFTNGP14.phx.gbl...
> What does running the following in your database return
> select current_user
> It sounds like you are the database owner (your login has been mapped to
> the dbo user in the database) since if you were simply a member of the
> db_owner role you would indeed have to prefic objects with dbo when
> creating them.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "msnews.microsoft.com" <Rene> wrote in message
> news:eDSuZ9oqFHA.3540@.TK2MSFTNGP14.phx.gbl...
>|||Jasper
Make sure you understand the difference between login names and user names.
Please read about them in the Books Online.
A login name is how you get access to SQL Server. Your login name is then
mapped to a user name in a particular database, to give you access to that
database within a SQL Server instance.
There are several ways you could have the username dbo. One is if you are
the true owner of the database. Another way is if you are in the sysadmin
server role. From your description, it sounds like this is not the case, as
that would give you WAY too much power.
A third way to have the username dbo is that your login name might be
aliased to the username dbo, which means you are not the real owner, but
within the database, you have all the privileges of the owner. A user
aliased to the dbo cannot do things outside the db, like increasing its
size.
In addition to current_user, you can also do the following:
sp_helpdb <name of db>
This will tell you who is the true owner of the db. Also
SELECT suser_sname()
will tell you what your login name is.
HTH
Kalen Delaney
www.SolidQualityLearning.com
<Rene> wrote in message news:uFPwhTpqFHA.3604@.tk2msftngp13.phx.gbl...
> Hey Japer:
> Well, I am away from the computer that has access to the database so I
> can't run the "select current_user" function right now, however, isn't me
> being the dbo a bad thing from the point of view of the hosting company?
> Wouldn't I be able to do things such as increase my database space or
> screw some other things up by me being a dbo? Especially since my database
> is on a shared SQL server?
> Thanks
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23VFQqKpqFHA.3540@.TK2MSFTNGP14.phx.gbl...
>|||Well, it turns out that the owner of the database is a user called
"dbcreator" which looks like is a predefine server role.
I am still kind of lost about this dbo:
1) You said "A third way to have the username dbo is that your login name
might be
aliased to the username dbo". Where can I see this alias mapping? Is there
an entry on some table that I can query with this iformation?
2) Is dbo an actual user? Can you logon to the database using dbo as your
logging name?
3) I was able to go to the database properties and change the "Restrinct
file grow" amount. What does this means? Does it mean that I am half dbo and
half restricted user?
Thanks
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OhE0HsqqFHA.4012@.TK2MSFTNGP12.phx.gbl...
> Jasper
> Make sure you understand the difference between login names and user
> names. Please read about them in the Books Online.
> A login name is how you get access to SQL Server. Your login name is then
> mapped to a user name in a particular database, to give you access to that
> database within a SQL Server instance.
> There are several ways you could have the username dbo. One is if you are
> the true owner of the database. Another way is if you are in the sysadmin
> server role. From your description, it sounds like this is not the case,
> as that would give you WAY too much power.
> A third way to have the username dbo is that your login name might be
> aliased to the username dbo, which means you are not the real owner, but
> within the database, you have all the privileges of the owner. A user
> aliased to the dbo cannot do things outside the db, like increasing its
> size.
> In addition to current_user, you can also do the following:
> sp_helpdb <name of db>
> This will tell you who is the true owner of the db. Also
> SELECT suser_sname()
> will tell you what your login name is.
> HTH
> Kalen Delaney
> www.SolidQualityLearning.com
> <Rene> wrote in message news:uFPwhTpqFHA.3604@.tk2msftngp13.phx.gbl...
>|||Well, I just learned something: Don't uncheck yourself out of the "db_owner"
permission or you will be doomed. I think that answers question "1", that
must be the way you alias your ID to the dbo ID and that explains why the
tables were created as dbo right?
Funny though, stored procedures are not created by default with dbo as the
owner, they are created using my user name...mmmm
Now lets figure out questions 2 and 3!
"Rene" <nospam@.nospam.com> wrote in message
news:uPwok7qqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Well, it turns out that the owner of the database is a user called
> "dbcreator" which looks like is a predefine server role.
> I am still kind of lost about this dbo:
> 1) You said "A third way to have the username dbo is that your login name
> might be
> aliased to the username dbo". Where can I see this alias mapping? Is there
> an entry on some table that I can query with this iformation?
> 2) Is dbo an actual user? Can you logon to the database using dbo as your
> logging name?
> 3) I was able to go to the database properties and change the "Restrinct
> file grow" amount. What does this means? Does it mean that I am half dbo
> and half restricted user?
> Thanks
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OhE0HsqqFHA.4012@.TK2MSFTNGP12.phx.gbl...
>|||I am perfectly clear about the distinction but maybe I didn't convey it too
well in my response (although it makes sense to me) :-)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OhE0HsqqFHA.4012@.TK2MSFTNGP12.phx.gbl...
> Jasper
> Make sure you understand the difference between login names and user
> names. Please read about them in the Books Online.
> A login name is how you get access to SQL Server. Your login name is then
> mapped to a user name in a particular database, to give you access to that
> database within a SQL Server instance.
> There are several ways you could have the username dbo. One is if you are
> the true owner of the database. Another way is if you are in the sysadmin
> server role. From your description, it sounds like this is not the case,
> as that would give you WAY too much power.
> A third way to have the username dbo is that your login name might be
> aliased to the username dbo, which means you are not the real owner, but
> within the database, you have all the privileges of the owner. A user
> aliased to the dbo cannot do things outside the db, like increasing its
> size.
> In addition to current_user, you can also do the following:
> sp_helpdb <name of db>
> This will tell you who is the true owner of the db. Also
> SELECT suser_sname()
> will tell you what your login name is.
> HTH
> Kalen Delaney
> www.SolidQualityLearning.com
> <Rene> wrote in message news:uFPwhTpqFHA.3604@.tk2msftngp13.phx.gbl...
>|||Rene,
Did you create the table using Enterprise Manager ? If so this would explain
it. From the subsequent information it is clear that you are (or were until
you removed yourself!) a member of the db_owner role in the database and are
neither the explicit database owner or aliased to the dbo user. Being a
member of the db_owner role does not mean you are aliased to the dbo user.
It also doesn't mean that objects you created will be owned by dbo however
when a member of the db_owner database role creates a table using Enterprise
Manager then it will be owned by dbo (which is slightly odd behaviour and
does not happen if you create a table in Query Analyzer without explicitly
specifying the owner)
1) Run exec sp_helpuser in the database. This will show up any aliases added
via sp_addalias as well as the list of database users and their associated
logins.
2) dbo is a special builtin user in every database and has implied
permissions to perform any action within that database. It cannot be
removed. When you connect to SQL Server you do so using a login (e.g. fred).
A login is used to control access to a SQL Server instance. To subsequently
access a database (ignoring system databases for the purposes of this
example) that login needs to be granted access to the database. The process
of granting access to a database via sp_adduser or sp_grantdbaccess creates
a user in the database mapped to your login. Whilst it is common for the
user name within the database to match the login, this is not required (for
example your login fred could be granted access to the database with a user
name of freduser). Another way of granting access (although not recommended)
is to use sp_addalias in which case your login is mapped to an existing user
in the database. So to answer the question <g> no you cannot login to SQL
Server using dbo because it is a database user not a login.
3) No it means you are a member of the db_owner role in the database.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rene" <nospam@.nospam.com> wrote in message
news:%23U7KKarqFHA.4076@.tk2msftngp13.phx.gbl...
> Well, I just learned something: Don't uncheck yourself out of the
> "db_owner" permission or you will be doomed. I think that answers question
> "1", that must be the way you alias your ID to the dbo ID and that
> explains why the tables were created as dbo right?
>
> Funny though, stored procedures are not created by default with dbo as the
> owner, they are created using my user name...mmmm
> Now lets figure out questions 2 and 3!
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uPwok7qqFHA.1032@.TK2MSFTNGP12.phx.gbl...
>|||Thanks Jasper
This ownership thing is just driving me crazy! I obviously need to do some
reading on the subject. For now at least I got the thing working.

This is the type of behavior that confuses everybody. My first instinct was
to thing that I should not have permissions to the table because is not mine
but I guess that users that belong to the db_owner group can do anything on
the database no matter who created the objects.
Oh well, I will definitely doing some research about it.
Thanks for your help.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OMM2cFyqFHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Rene,
> Did you create the table using Enterprise Manager ? If so this would
> explain it. From the subsequent information it is clear that you are (or
> were until you removed yourself!) a member of the db_owner role in the
> database and are neither the explicit database owner or aliased to the dbo
> user. Being a member of the db_owner role does not mean you are aliased to
> the dbo user. It also doesn't mean that objects you created will be owned
> by dbo however when a member of the db_owner database role creates a table
> using Enterprise Manager then it will be owned by dbo (which is slightly
> odd behaviour and does not happen if you create a table in Query Analyzer
> without explicitly specifying the owner)
> 1) Run exec sp_helpuser in the database. This will show up any aliases
> added via sp_addalias as well as the list of database users and their
> associated logins.
> 2) dbo is a special builtin user in every database and has implied
> permissions to perform any action within that database. It cannot be
> removed. When you connect to SQL Server you do so using a login (e.g.
> fred). A login is used to control access to a SQL Server instance. To
> subsequently access a database (ignoring system databases for the purposes
> of this example) that login needs to be granted access to the database.
> The process of granting access to a database via sp_adduser or
> sp_grantdbaccess creates a user in the database mapped to your login.
> Whilst it is common for the user name within the database to match the
> login, this is not required (for example your login fred could be granted
> access to the database with a user name of freduser). Another way of
> granting access (although not recommended) is to use sp_addalias in which
> case your login is mapped to an existing user in the database. So to
> answer the question <g> no you cannot login to SQL Server using dbo
> because it is a database user not a login.
> 3) No it means you are a member of the db_owner role in the database.
>
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Rene" <nospam@.nospam.com> wrote in message
> news:%23U7KKarqFHA.4076@.tk2msftngp13.phx.gbl...
>