Re: conflicting object names in sql server 2000

From: Sylvain Lafontaine ("Sylvain)
Date: 01/09/05 

Date: Sat, 8 Jan 2005 19:50:39 -0500

Here a suggestion: try to add another user (with the role db_owner set?) and
use this account to connect. As this new account will have a different user
name, SQL-Server shouldn't look anymore for any object with michael as the
owner.

S. L.

"zx6er93" <zx6er93@discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@microsoft.com...
> 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
    ... The owner of the object will depend on the user ... On the local database server when it does the select * from ... she must specify the owner: ...
    (microsoft.public.sqlserver.server)
  • Re: User names
    ... I don't have a pre-sp3 server to try this on, but in sp3, if you are a user ... byjust specifically giving the owner name in QA. ... > dbo was the owner. ...
    (microsoft.public.sqlserver.server)
  • Re: not creating tables as dbo anymore ?
    ... Database Owner (dbo) ... existing user ID in the database specified by database_name. ... with the login of the current connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Change UDF Owner
    ... Does it exist in the master database? ... Users can be put in the dbo role, but dbo will still own objects ... concept of 'schema' as 'owner' rather than just schema as DDL is exploited ... to the appropriate Fixed Server Roles or whatever is applicable. ...
    (microsoft.public.sqlserver.security)