Re: logged in user can't see tables

From: John Bell (jrm_bell@hotmail.com)
Date: 08/31/02 

From: John Bell <jrm_bell@hotmail.com>
Date: Sat, 31 Aug 2002 22:08:32 +0100

Hi

From:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_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/ad_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

  • Re: logged in user cant see tables
    ... Are you connecting to the correct database? ... >When I connect to my SQL 2000 database through a DSN in my .asp page using ... >the owner name. ... my DSN is set to authenticate with a SQL ...
    (microsoft.public.sqlserver.security)
  • Re: logged in user cant see tables
    ... I assume I am connecting to the correct database, as when I select with the ... database with an employees table on my server). ... specify the the owner name. ... I just didn't expect I would HAVE to specify the ...
    (microsoft.public.sqlserver.security)
  • Re: Missing table
    ... execute the commands within Query Analyzer. ... The first piece of sql displays the table owner, type, and the name of the table. ...
    (microsoft.public.sqlserver.server)
  • RE: Is the Folowing Applicationb Possible
    ... Therfore are you saying it is possible to create a useable app ... that uses SQL properly - but based around Sharepoint 'front end' AND is easy ... Then have a drop down list so that a users could easliy pick the owner. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: DAP & SQL 2000
    ... I imported everything I had in Access to SQL, ... everything I imported has the owner "quote" ... Even tho I imported 3 tables I only use 1 (which does have a primary key) ... > on the Data Outline view and, in the case of the source, has the owner be ...
    (microsoft.public.access.dataaccess.pages)