Re: Confused about dbo

• Previous message: Jasper Smith: "Re: Confused about dbo"
• In reply to: Jasper Smith: "Re: Confused about dbo"
• Next in thread: Jasper Smith: "Re: Confused about dbo"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Sat, 27 Aug 2005 14:04:14 -0500

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.

>> however when a member of the db_owner database role creates a table using
>> Enterprise Manager then it will be owned by dbo .

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

• Previous message: Jasper Smith: "Re: Confused about dbo"
• In reply to: Jasper Smith: "Re: Confused about dbo"
• Next in thread: Jasper Smith: "Re: Confused about dbo"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]