Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    2

    Unanswered: How can a user use other user tables without writing the owner in "select" sentence

    Hello:

    The owner of the tables is not written in the sql code in my on line application (this application is a migration from IBM mainframe with DB2 to W2K with SQLServer and this is not a problem in mainframe).
    The database owner is MV2D.
    So the ODBC accessing in the developer PCs is MV2D. I do not like this solution because is not secure. I cannot change the code of my application because I am developing in the two operating systems with a unique code.
    I know that if Ichange the database owner to dbo whoever will see the tables without saying the owner, but in the other hand the batch part of my application will not work because the table owner is written in this part. Is there any choice to access to other user tables without writting the owner of the table, in the profile, ...?
    Thanks in advanced,

    Cristina

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post

    RE: Hello: The owner of the tables is not written in the sql code in my on line application (this application is a migration from IBM mainframe with DB2 to W2K with SQLServer and this is not a problem in mainframe). The database owner is MV2D. So the ODBC accessing in the developer PCs is MV2D. I do not like this solution because is not secure. I cannot change the code of my application because I am developing in the two operating systems with a unique code. I know that if I change the database owner to dbo whoever will see the tables without saying the owner, but in the other hand the batch part of my application will not work because the table owner is written in this part. Is there any choice to access to other user tables without writting the owner of the table, in the profile, ...?
    Thanks in advanced, Cristina By:

    Q1 Is there any choice to access to other user tables without writting the owner of the table?
    A1 Generally, Yes. Given an object in database, DB, with dboo = MV2D; e.g.(DB.MV2D.TableObject)
    i Create the view: DB.dbo.TableObject (view as a select of DB.MV2D.TableObject)
    ii Select DB..TableObject (yields access to DB.MV2D.TableObject without writing the owner of the table)

    Q2 [The database owner is MV2D. So the ODBC accessing in the developer PCs is MV2D. I know that if I change the database owner to dbo whoever will see the tables without saying the owner]
    A2 Its not so clear what the situation is here. It seems to mean to say:
    [EXEC sp_changedbowner 'dbo'] OR is MV2D just in DB dbo role (for the user DB) or...

  3. #3
    Join Date
    Oct 2002
    Posts
    2
    Originally posted by DBA
    RE: Hello: The owner of the tables is not written in the sql code in my on line application (this application is a migration from IBM mainframe with DB2 to W2K with SQLServer and this is not a problem in mainframe). The database owner is MV2D. So the ODBC accessing in the developer PCs is MV2D. I do not like this solution because is not secure. I cannot change the code of my application because I am developing in the two operating systems with a unique code. I know that if I change the database owner to dbo whoever will see the tables without saying the owner, but in the other hand the batch part of my application will not work because the table owner is written in this part. Is there any choice to access to other user tables without writting the owner of the table, in the profile, ...?
    Thanks in advanced, Cristina By:

    Q1 Is there any choice to access to other user tables without writting the owner of the table?
    A1 Generally, Yes. Given an object in database, DB, with dboo = MV2D; e.g.(DB.MV2D.TableObject)
    i Create the view: DB.dbo.TableObject (view as a select of DB.MV2D.TableObject)
    ii Select DB..TableObject (yields access to DB.MV2D.TableObject without writing the owner of the table)

    Q2 [The database owner is MV2D. So the ODBC accessing in the developer PCs is MV2D. I know that if I change the database owner to dbo whoever will see the tables without saying the owner]
    A2 Its not so clear what the situation is here. It seems to mean to say:
    [EXEC sp_changedbowner 'dbo'] OR is MV2D just in DB dbo role (for the user DB) or...
    Your first idea Q1 have solved the problems with the on line and batch.
    The Q2 problems has solved too. Now developers had the ODBC with Windows Auhentifications and it works properly.
    Thank you very much for your helping.

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    RE: Your first idea Q1 have solved the problems with the on line and batch. The Q2 problems has solved too. Now developers had the ODBC with Windows Auhentifications and it works properly.
    Thank you very much for your helping.

    You are welcome.

    One point I might add; Making future schema changes to the base tables, e.g., the (DB.MV2D.TableObject) object, that the views, e.g., the (DB..TableObject ) view, access; may also require altering / recreating the views.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •