none
Problem in assigning custom schema as default schema

    Question

  • Dear All 

    I have one online application which is running perfectly, i got the db backup and try to run the application offline. When i run the application locally the system did not locate the store procedure name after analysis i found because of custom schema name (assigned by hosting) it is not identify the sp and table name 

    I found following items in the db

    1) There are some tables and sp which is under DBO schema 

    2) There are custom tables, sp and views which are with the custom schema 

    What i have done before posting 

    1) I checked out the DB security and find the user the user have a same schama 

    2) The application is running with the same db user name

    Major difference between local and online db 

    In local db i can't able to access table name with out schema name (myshcmea.tablename) but in online the same table i can eaisly access with out schema name (tablename) 

    what i understand that my custom schema is unable to set as a default schema under my restored database 

    I need some solution very urgent i worked on it last 7 days tried very thing but failed 

    Can any one tell me how can i resolve this issue 

    Note: I already tried to change the custom schema to dbo but after that applciation giving error becuase it don't identity the custom schema with many table 

    Waiting for your reply and its really urgent 


    RB

    Tuesday, May 15, 2012 11:09 PM

Answers

  • i want to exectue the query like this select * from tablename 

    currently after doing every updates it still not access directly if i access it i need to write custom schema name in my query before table 

    select * from myschma.tablename


    Hi Rashid Imran Bilgrami,

    Thank you for your update.

    Regarding to your description, seems the current user’s default schema is not mapping to the object default schema.

    You can check your current user default schema following steps as >> Security>> logins>>current login properties

    User owning a schema is different to a default schema of an user. An user can own multiple schemas but an user can have only one default schema. So its better to use Specified schema in case an user can query different objects in different default schema.

     


    Regards, Amber zhang

    Tuesday, May 22, 2012 7:56 AM
    Moderator

All replies

  • i found one thing may be this is creating a  problem 

    When i compare local db and the online db i found 1 difference under database > Options > Permissions >Grantor Name 

    The Grantor column shows me the dbo but in online server it shows the custom schema 

    How can i change this dbo with the custom schema any idea

    Regards 


    RB

    Wednesday, May 16, 2012 8:30 AM
  • Hi Rashid Imran Bilgrami,

    Thank you for your update.
    >> When i compare local db and the online db i found 1 difference under database > Options > Permissions >Grantor Name
    The Grantor column shows the current login’s schema.
    For more information, please refer to sys.database_permissions (Transact-SQL)

    >> How can i change this dbo with the custom schema any idea
    If you want to change schema you can refer to this article about ALTER SCHEMA (Transact-SQL)

     


    Regards, Amber zhang

    Thursday, May 17, 2012 6:46 AM
    Moderator
  • Thanks for your reply 

    >> When i compare local db and the online db i found 1 difference under database > Options > Permissions >Grantor Name
    The Grantor column shows the current login’s schema. 
    For more information, please refer to sys.database_permissions (Transact-SQL)

    This one i will check and get back here (thanks for the reply) 

    >> How can i change this dbo with the custom schema any idea

    If you want to change schema you can refer to this article about ALTER SCHEMA (Transact-SQL)

    I already set this through script and the GUI interface and it shows me the default schema as my custom schema, but when i tried to run the query it still ask me the schema name before the Table or SP :( 

    i want to exectue the query like this select * from tablename 

    currently after doing every updates it still not access directly if i access it i need to write custom schema name in my query before table 

    select * from myschma.tablename 



    RB

    Thursday, May 17, 2012 9:43 AM
  • i want to exectue the query like this select * from tablename 

    currently after doing every updates it still not access directly if i access it i need to write custom schema name in my query before table 

    select * from myschma.tablename


    Hi Rashid Imran Bilgrami,

    Thank you for your update.

    Regarding to your description, seems the current user’s default schema is not mapping to the object default schema.

    You can check your current user default schema following steps as >> Security>> logins>>current login properties

    User owning a schema is different to a default schema of an user. An user can own multiple schemas but an user can have only one default schema. So its better to use Specified schema in case an user can query different objects in different default schema.

     


    Regards, Amber zhang

    Tuesday, May 22, 2012 7:56 AM
    Moderator
  • Thanks the your post helps me alot, i have done one mistake i tried to connect with the restored users and the problem arrived there because this user is not the global user 

    I just create one global user and assign the schema and database with it after that when i login it works perfectly ;) 

    I post a blog on it 

    http://bestvisualization.blogspot.com/2012/05/how-to-assign-db-custom-schema-in-sql.html

    Regards 

    Rashid Bilgrami 


    RB

    Sunday, May 27, 2012 9:46 AM