Re: logged in user can't see tables

From: pablo (a@b.com)
Date: 09/03/02 

From: "pablo" <a@b.com>
Date: Tue, 03 Sep 2002 01:22:06 GMT

John,

Thanks for all the help. I managed to solve the problem. It turns out that
if a user has sysadmin privileges, then it logs in as if it were dbo. So it
has to specify the owner name, as if it were logging in as "sa". Your
second link sort of said that, but didn't really (it says it will create a
table as dbo, but did not say that it would not be able to see objects
created by its name). What is weird is that this user has had sysadmin
privileges for a month, and I did not have this problem. Or did it? I
guess I need more sleep.

Again, Thanks for spending the time.

-Paul

"John Bell" <jrm_bell@hotmail.com> wrote in message
news:s266nugv08sfjflq41nqcvganpb4c6tvci@4ax.com...
> Hi
>
> Under normal circumstances if paul created a table he would need
> db_ddladmin privilege. Paul, the database owner, sysadmins, users with
> db_datareader and specifically allocated users can see the data in the
> table. Paul should not need to prefix the table with paul, but other
> users should. Try running the following as paul and see what happends.
>
> SELECT @@VERSION
>
> sp_helpuser 'dbo'
>
> sp_helpuser 'paul'
>
> create table employee ( firstname varchar(20),
> surname varchar(20) )
>
> create table dbo.employee ( firstname varchar(20),
> surname varchar(20) )
>
> insert into employee ( firstname ,
> surname ) VALUES ( 'Not' , 'Qualified' )
>
> insert into paul.employee ( firstname ,
> surname ) VALUES ( 'paul' , 'Qualified' )
>
> insert into dbo.employee ( firstname ,
> surname ) VALUES ( 'dbo' , 'Qualified' )
>
> PRINT 'Not Qualified'
> SELECT * FROM employee
>
> PRINT 'Paul Qualified'
> SELECT * FROM paul.employee
>
> PRINT 'dbo Qualified'
> SELECT * FROM dbo.employee
>
>
> John
>
>
> On Mon, 02 Sep 2002 02:33:12 GMT, "pablo" <a@b.com> wrote:
>
> >John,
> >
> >Thanks again for responding. I am quite sure that it is not a typo.
That
> >is why I am posting in the security group. There are only two users on
this
> >SQL server, "sa" and "paul". So, when "paul" logs in, and can see all
the
> >rows in "paul.employees", than I have to assume that, in fact, "paul" is
> >logged in. Furthermore, "paul" is the owner of the tables in question,
and
> >not "dbo". Only a user can be an owner, so they are, in fact, owned by
> >"paul" (since there are no other users on the server). So the question
> >still stands. Why can't "paul" see the tables owned by "paul" when he
logs
> >in? There must be some other reason. Like I flipped some switch
somewhere
> >in NT that let "paul" log in as "sa" or something.
> >
> >-Paul
> >
> >
> >"John Bell" <jrm_bell@hotmail.com> wrote in message
> >news:3mh4nu878eum846ircigpf426422kn1skl@4ax.com...
> >> Hi Paublo
> >>
> >> These are just guesses as the behavour is not what I experience or as
> >> documented:
> >>
> >> On the second part I think that you that you may still be using the
> >> connection that you logged in with when user paul had the privileges.
> >> If you logged out and back in then I think the owner of the table
> >> would be as described in the article.
> >>
> >> As far as your first point... the only thing I can think of is a typo
> >> when you tested the name or when you created the table, or
> >> alternatively you are not actually the user paul when you test it.
> >>
> >> John
> >>
> >> On Sat, 31 Aug 2002 22:47:58 GMT, "pablo" <a@b.com> wrote:
> >>
> >> >John,
> >> >
> >> >Thanks for the quick reply. The first link is how I expect it to
work.
> >But
> >> >when I log in as the user 'paul', I still have to provide a owner name
to
> >> >reference the table (paul.employees), otherwise I get the object not
> >found.
> >> >Now the question is, why? Any ideas what I should look at?
> >> >
> >> >The second link explains why the when 'paul' creates a table, it is
owned
> >by
> >> >dbo. Interestingly enough, when I remove sysadmin privileges from
> >'paul',
> >> >when I log in as that user to create a table, is still creates it with
> >dbo
> >> >as the owner. According to the second link (if I read it correctly),
it
> >was
> >> >supposed to be created as owned by 'paul'. But this issue is
secondary.
> >I
> >> >really need to solve the first problem.
> >> >
> >> >Thanks,
> >> >
> >> >-Paul
> >> >
> >> >
> >> >"John Bell" <jrm_bell@hotmail.com> wrote in message
> >> >news:inb2nucs2tum2tkgqfnugps59fb4g0k4nq@4ax.com...
> >> >> Hi
> >> >>
> >> >> From:
> >> >>
> >> >>
> >>
>
>>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/
a
> >d
> >> >_security_2sz6.asp
> >> >>
> >> >> When users access an object created by another user, the object
should
> >> >> be qualified with the name of the object owner; otherwise,
Microsoft®
> >> >> SQL Server may not know which object to use because there could be
> >> >> many objects of the same name owned by different users. If an object
> >> >> is not qualified with the object owner when it is referenced (for
> >> >> example, my_table instead of owner.my_table), SQL Server looks for
an
> >> >> object in the database in the following order:
> >> >>
> >> >> Owned by the current user.
> >> >> Owned by dbo.
> >> >>
> >> >> If the object is not found, an error is returned.
> >> >>
> >> >>
> >> >> Also check out
> >> >>
> >> >>
> >>
>
>>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/
a
> >d
> >> >_security_9qyh.asp
> >> >>
> >> >>
> >> >> John
> >> >>
> >> >> On Sat, 31 Aug 2002 18:56:20 GMT, "pablo" <a@b.com> wrote:
> >> >>
> >> >> >John,
> >> >> >
> >> >> >I am sorry. You are absolutely right. It was very late and I was
> >tired
> >> >of
> >> >> >working on this for so long.
> >> >> >
> >> >> >The error I get with the "SELECT * FROM employees" (without the
table
> >> >owner
> >> >> >name) is:
> >> >> >
> >> >> >[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> >> >> >'employees'.
> >> >> >
> >> >> >I assume I am connecting to the correct database, as when I select
> >with
> >> >the
> >> >> >owner name prefixed, I get the expected data back (and there is no
> >other
> >> >> >database with an employees table on my server).
> >> >> >
> >> >> >The user has full permissions on all tables. I can read it fine
when
> >I
> >> >> >specify the the owner name. I just didn't expect I would HAVE to
> >specify
> >> >the
> >> >> >owner name, as I am connected as said owner.
> >> >> >
> >> >> >I have quadruple-checked the DSN, and it specifies to connect with
SQL
> >> >> >login, defaults to the correct database, and, as I said before,
fails
> >> >login
> >> >> >unless I specify the user that is the owner of the table.
> >> >> >
> >> >> >One thing that I have noticed is that when I connect to the
database
> >> >using
> >> >> >SEM, it appears that, although I registered to connect as the same
> >user
> >> >> >mentioned above, when I try and run queries without the owner
prefix,
> >I
> >> >get
> >> >> >the same Invalid Object Name error message. It is as if I am
logging
> >in
> >> >as
> >> >> >sa, even though I have logged in as 'paul' (which it the real
> >username).
> >> >> >
> >> >> >Thanks for your help.
> >> >> >
> >> >> >-Paul
> >> >> >
> >> >> >
> >> >> >"John Bell" <jrm_bell@hotmail.com> wrote in message
> >> >> >news:sav0nug92m2psme0cosv0fnsn7nrf36rlg@4ax.com...
> >> >> >> You don't say what the error messages are!
> >> >> >>
> >> >> >> Are you connecting to the correct database?
> >> >> >>
> >> >> >> What permissions does the user has on the tables?
> >> >> >>
> >> >> >>
> >> >> >> John
> >> >> >>
> >> >> >> On Sat, 31 Aug 2002 04:34:09 GMT, "pablo" <a@b.com> wrote:
> >> >> >>
> >> >> >> >When I connect to my SQL 2000 database through a DSN in my .asp
> >page
> >> >> >using
> >> >> >> >SQL authentication, I cannot see my tables without first
prefixing
> >> >them
> >> >> >with
> >> >> >> >the owner name. For example, my DSN is set to authenticate with
a
> >SQL
> >> >> >> >id/pass:
> >> >> >> >
> >> >> >> >DSN=mydsn;UID=owner;pwd=xxx
> >> >> >> >
> >> >> >> >when I try and select on a table owned by owner
> >> >> >> >
> >> >> >> >SELECT * FROM employees
> >> >> >> >
> >> >> >> >fails, and
> >> >> >> >
> >> >> >> >SELECT * FROM owner.employees
> >> >> >> >
> >> >> >> >works.
> >> >> >> >
> >> >> >> >I tried the DSN without the UID and PWD, and the login fails, so
I
> >am
> >> >> >sure I
> >> >> >> >am loggin in correctly.
> >> >> >> >
> >> >> >> >I would like not to have to specifiy the owner prefix. Any
ideas?
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>
>

Relevant Pages