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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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