Post reply

SCHEMA ISSUE

  • March 2, 2011 at 6:12 pm

    #234601

    Hi All,

    I took the backup in sqlserver 2000 database and restore into sqlserver 2008. Then created a user login and mapped into database user.

    previous setup in sqlserver 2000

    login name : stubrand

    password : *******

    db name : stu_ods

    user : stubrand

    Many of the objects comes under stubrand only. like

    tables

    stubrand.facttable

    stubrand.rawdata

    stubrand.usp_getretrivaldata

    i used to stubrand login credentials and stu_ods database. then i ran a

    select * from facttable.. query results will come..

    but i used another credentials.. then i used the following

    select * from stubrand.facttable..

    But my problem is In Sqlserver 2008 only

    I used the same stubrand login credentials in sqlserver 2008..

    I ran the select query

    select * from facttable.. i got an error.

    "Invalid object Name".

    then

    select * from stubrand.facttable.. (its work)

    I should not give schemaname using the same credentials.

    Please let me know, why the error came. and how to fix it.

    It would be very greatful, if anybody helps me.

    Regards

    Balaji G

  • March 2, 2011 at 7:02 pm

    #1293821

    hi Balaji, in SQL 2008, can you double click the SQL Login stubrand and check the mappings tab.

    Check if the schema value next to database name is blank or dbo. If so enter stubrand.

    Then when you login using stubrand login, the default schema will be stubrand and you will be able to execute without using schema. While using any other credentials, you will still use the schema name as usual.

    If you have a default schema and you are connected to the right database, you wont need the full extension. In that case you can enter select * from tablename

    But, the general execution syntax is Select * from DBName.SchemaName.TableName.

  • March 2, 2011 at 7:49 pm

    #1293826

    Schemas aren't automatically bound to users in SQL Server 2005 and later. They were in SQL 2000, but not any more.

    You'll need to associate the user with a default schema for the one you want.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 2, 2011 at 10:28 pm

    #1293848

    wayne.r (3/2/2011)

    hi Balaji, in SQL 2008, can you double click the SQL Login stubrand and check the mappings tab.

    Check if the schema value next to database name is blank or dbo. If so enter stubrand.

    Then when you login using stubrand login, the default schema will be stubrand and you will be able to execute without using schema. While using any other credentials, you will still use the schema name as usual.

    If you have a default schema and you are connected to the right database, you wont need the full extension. In that case you can enter select * from tablename

    But, the general execution syntax is Select * from DBName.SchemaName.TableName.

    Hi

    Can you please check the sample login and userscreen jpeg attachment. which is related to schema issue. default schema is stubrand only. but still i cannot access the table with out using schema.

    Please let me know how to fix.

    Thanks for your quick response.

    Regards

    Balaji G

Viewing 4 posts - 1 through 3 (of 3 total)

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