Re: JDBC/ODBC user issues accessing SQL Server 2000 database

From: Sampsa Sohlman (sampsa_sohlman_at_hotmail.com)
Date: 12/27/03

  • Next message: Lee Fesperman: "Re: counting days in a query" 
    Date: Sat, 27 Dec 2003 14:31:49 +0200

    ITM wrote:

    > I have a SQL Server 2000 database which has been restored from a
    > backup. The restored database contained a user called "X", which is
    > the owner of all of the tables in the database. When I try to execute
    > an SQL SELECT against any of the tables in the database (using the
    > JDBC/ODBC bridge) I get an "invalid object name" error - it seems to
    > be requiring me to prefix the table names with "X.", even though the
    > ODBC data source has been set up with the user "X".
    >
    > If I use MS-Access to link to the database using the same ODBC data
    > source I can see the tables - each is listed with an X. prefix.
    >
    > If I connect from the SQL Server command line using the X user/login I
    > can refer to tables without using the X prexix on table names.
    >
    > The only thing that may have complicated things is that I used
    > sp_change_users_login to associate the X user with the X login.
    >
    > Can anyone tell me how I can avoid having to prexix my table names
    > with the user name, when I should be connecting with that user name?

    To avoud prefixes on table names, they have to be owned by 'dbo'.

    To change existing tables to dbo use sp_changeobjectowner procedure.

    Example: myuser.mytable -> dbo.mytable

    sp_changeobjectowner 'myuser.mytable', 'dbo'

    If you want to create these tables by default you need that user id that
    you are using is configured for that. If you are sa level user it is
    done automaticly, but if you want to use different user id, your user id
    has to be made dbo, by using sp_changedbowner procedure. Your database
    adminstrator has to execute that procedure to your user id.

    More information on SQL Server Books online.

    Other things :) There are quite good JDBC drivers from Microsoft
    available to MS-SQL Server or you can try open source
    http://jtds.sourceforge.net driver which is good. They are better option
    than JDBC/ODBC bridge.

    Merry christmast and happy new year.

    Sampsa 

    -- 
     -------------------------------------------
    // Sampsa Sohlman                        //
   //  My email can be found on my homepage //
  //   http://sampsa.sohlman.com           //
-------------------------------------------
  • Next message: Lee Fesperman: "Re: counting days in a query"

    Relevant Pages