Re: conflicting object names in sql server 2000

From: zx6er93 (zx6er93_at_discussions.microsoft.com)
Date: 01/08/05 

Date: Sat, 8 Jan 2005 13:37:04 -0800

Hi,

  Yes, this is very confusing, unfortunately this is what I have to work
with. Thanks for responding though.

  the connection string specifies "michael" as the user when connecting to
the database. On the local database server when it does the select * from
xxx it is expecting to see the table which is owned by dbo, and it does
produce these results. When it looks at the function, it does it with
michael.xxx Note that michael is the owner of the database. I have
attempted to detach the database and reattach it on another server and it
works like that as well. However the server I need to get these tables and
functions to I do not have access to reattach the database, only to move the
database over via dts. The only clue I can find is the difference in the
sp_help results which I provided earlier.

Thanks again.

"Kalen Delaney" wrote:

> Hi
>
> This is a bit confusing. Are you trying to access the table or the function?
> What user are YOU when you try to access the object?
>
> Here are a couple of points that may help:
>
> if you say
>
> SELECT * from xxxx
>
> you are selecting from a table. The only question is, which table? SQL
> Server will first try to select from a table xxx that YOU own, if there is
> none, it will see if there is one owned by dbo.
> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> dbo, or anybody besides Michael, wants to access the table owned by
> Michael, she must specify the owner:
>
> SELECT * from michael.xxx
>
> If you are selecting from a table valued function, you MUST specify an
> owner, PLUS you must specify a parameter list, even if there are no
> parameters.
>
> So,
> SELECT * from xxx()
> is incorrect syntax because you didn't specify the owner
>
> SELECT * from dbo.xxx()
> will access the xxx FUNCTION owned by dbo
>
> and
>
> SELECT * from michael.xxx()
> will access the xxx FUNCTION owned by michael
>
>
> I hope this helps. If not, please clarify
>
> What object you are trying to access, and if it's a function, what are the
> parameters
> Who owns that object
> What user you are
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "zx6er93" <zx6er93@discussions.microsoft.com> wrote in message
> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@microsoft.com...
> >I can't seem to solve this problem. I hope someone can help. I have a
> >table
> > which is owned by dbo called xxx. I also have a user define function also
> > called xxx but owned by a different user. Everything works fine on the
> > local
> > database. From query analyzer if I type in select * from xxx everything
> > works fine on the local database.
> >
> > Here's the problem. When I use dts to move this over to another server,
> > and
> > then try select * from xxx it returns the error "Server: Msg 208, Level
> > 16,
> > State 3, Line 1
> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> >
> > Unfortunately because of the situation I can't go back in and change the
> > user function name to something else, or change all the code to prefix the
> > table with dbo.xxx.
> >
> > The only clue I've come up is that on the local machine when I do an
> > sp_help
> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> >
> > but when I do an sp_help on the other server it shows "xxx michael table
> > function 2005-01-08 13:21:57.500"
> >
> > that seems to explain my problem, but my question is how/what can I update
> > so that the table gets the "higher priority" so when I do the select it
> > looks
> > at the table and not the function when just using xxx and not dbo.xxx or
> > michael.xxx?
> >
> > Hope that makes sense. I'm in a real bind with this and would appreciate
> > any help. Thanks.
> >
> > John
> >
> >
> >
>
>
>

Relevant Pages

  • Re: How to prevent DELETEs in a table
    ... It is the dbo database USER, not server-level groups, that determins ... It has implicit permissions that can not be denied. ... SQL Server just skips any permission validation for sysadmins. ...
    (microsoft.public.sqlserver.server)
  • Re: conflicting object names in sql server 2000
    ... On the local database server when it does the select * from ... > michael.xxx Note that michael is the owner of the database. ... it will see if there is one owned by dbo. ...
    (microsoft.public.sqlserver.server)
  • Re: Security Problem with AD Group in SQL Server Security Logins area
    ... role they were mapped to the dbo user in each database. ... Jasper Smith (SQL Server MVP) ... >> system role of "System Administrators." ...
    (microsoft.public.sqlserver.security)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: Database/WebAccess
    ... Regarding your synchronisation question of the local database with the ... remote internet database question. ... With SQL server you can define a Linked Server and write the necesssary ...
    (microsoft.public.backoffice.smallbiz2000)