Post reply

invalid object

  • July 19, 2006 at 12:15 pm

    #60935

    Using sql 2000

    Connected to SQL QA as user 'x' who is db_owner and select from objects owned by object_owner 'x'. Getting invalid object error message. If the object is specifically prefixed as select * from x.(objname), query works fine. Why is this needed, if the connx is being made in context of the user x ?

    Also select current_user returns dbo instead of x.  We did not have this pb before. Any inputs on this issue will be appreciated.

    Thanks

  • July 19, 2006 at 1:55 pm

    #650232

    In Enterprise Manager in Users of your database check what login is mapped to the DBO

    This is correct that if you login as a login who is mapped to the DBO user then your current_user will retrurn dbo

    In QA you may do:

    select user_id(),user_name(), suser_sname()

    Also do

    select uid,name from sysobjects where name = 'YourObjectName'

    and see if these User Ids match. DBO should have user ID =1

    Regards,Yelena Varsha

  • July 19, 2006 at 2:10 pm

    #650238

    Thanks for your reply.

    The user X is not mapped to dbo. I even removed dbo role from this user. I am making the connx to SQL QA using X. X is the object owner. When I run query select * from objectname, I get an error and the select current_user still shows dbo. Why is that ? But when I prefix the object with X, I get the results. I dont und why this is happening.. Please let me know. Thanks again for your help

  • July 19, 2006 at 2:15 pm

    #650242

    Do the rest of the queries. SUSER_SNAME will show you the login name. There could be one case that may lead to your situation:

    Windows User X and SQL Server Standard login X. They are 2 different people. SQL User X may be the object owner and you connect as Windows user X.

    Regards,Yelena Varsha

  • July 19, 2006 at 2:31 pm

    #650246

    I bet your user is aliased!!!

    run sp_dropalias on the login

    and your queries should perform as you expect

     


    * Noel

  • July 19, 2006 at 2:38 pm

    #650251

    The user is not aliased and It is the only SQL login

  • July 19, 2006 at 2:46 pm

    #650253

    did you run :

    exec sp_dropalias @loginame = 'THE-SQL-LOGIN' on that database ?


    * Noel

  • July 20, 2006 at 1:43 am

    #650321

    Hi, my thoughts on the subject, not sure if they will be of any help...

    If 'x' is the only login, and current_user returns dbo, then 'x' must be a member of the sysadmin fixed server role. Any objects created by 'x' will automatically be owned by dbo. Therefore, if the objects are owned by 'x', you must have either created them and used sp_changeobjectowner to change ownership to 'x', or created them when 'x' wasn't a member of sysadmin, or created them using a different user called 'x'.

    I had a similar problem when I migrated some objects from a test to a production server, because a login 'x' on one database server is not necessarily the same as login 'x' on another server, something to do with the SIDs I think. I resolved it by using sp_changeobjectowner to change ownership to dbo. Anything owned by dbo is visible to all users, but SQL Server searches the current users objects first, before it searches the dbo schema, so it is worth referencing the object as dbo.objectname to avoid confusion, and save the odd millisecond here or there.

    David

    If it ain't broke, don't fix it...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply