Re: Confused about dbo
From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 08/27/05
- Previous message: Jasper Smith: "Re: Confused about dbo"
- In reply to: Rene: "Re: Confused about dbo"
- Next in thread: Rene: "Re: Confused about dbo"
- Reply: Rene: "Re: Confused about dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Sat, 27 Aug 2005 17:07:15 +0100
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: Rene: "Re: Confused about dbo"
- Next in thread: Rene: "Re: Confused about dbo"
- Reply: Rene: "Re: Confused about dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
