0

I have changed default schema of a database from dbo to something else but when I wrote query it still took dbo as default schema

I am using Microsoft SQL server 2008 and have some problems.Please help me on this:

I have a database which has two schemata, one is dbo and another one is studentlinkweb.

I was logging in as studentlinkweb. At first, the default schema is dbo, then I had already successfully set default schema from dbo to schema studentlinkweb for the login studentlinkweb. (I have already checked it under

database_name>Security>Users>properties(right click)> general and

under Database>Security>Logins>loginID>properties(right click)>User Mapping)

However, when I wrote SQL script it did not work as expected. to be specific:

I have a table names table_name under schema studentlinkweb.

When I wrote Select * from table_name and ran, it returned a message: Invalid object name 'table_name'

when I wrote Select * from studentlinkweb.table_name and ran, the query excecuted sucessfully.

I have another table names table_name2 under schema dbo,

When I wrote Select * from table_name2 and ran, the query excecuted sucessfully. . Here I didn't specify the schema but the query still ran successfully

This meant that the default schema was still dbo.

Could you please give me some idea or suggestion?

Tony Trinh Duy Tung
  • 21
  • 5
  • What product and/or tool were you doing this clicking with? What is the database schema? What is the data? What does "it worked" mean? We cannot read your mind. Go to StackExchange banner>help>Help Center>How do I ask a good question? and read. – philipxy Jul 27 '14 at 00:25
  • Oh, so sorry. Here are the details: – Tony Trinh Duy Tung Jul 28 '14 at 02:33
  • Sorry philipxy, I have rewritten the question, following the guide from Help Center. – Tony Trinh Duy Tung Jul 28 '14 at 02:54
  • 1. Info is better. (Including the new tag.) Still, per my last comment: I didn't mean just the names of the schemas but the schemas themselves, ie their DDL for tables, constraints etc for a minimal example with your problem. What is the data? (The latter might not be necessary given your problem seems to be pre-runtime.) 2. You haven't actually asked a clear question in your question. Finally I see you put it in your title. Please put it in the body appropriately. PS have you googled "stackoverflow sql server 2008 change default schema"? – philipxy Jul 28 '14 at 03:50
  • Hi Philipxy, I have googled a lot. I am quite new to this server so I am not sure about "I didn't mean just the names of the schemas but the schemas themselves, ie their DDL for tables, constraints etc for a minimal example with your problem". Could you please be clearer? – Tony Trinh Duy Tung Jul 29 '14 at 01:38
  • Hi. I have already found my answer in this link: http://stackoverflow.com/questions/8208019/how-to-set-the-default-schema-of-a-database-in-sql-server-2005/8208124#8208124. The reason is that I had set the server role for user to be sysadmin. Therefore I could not change the default schema of the users. – Tony Trinh Duy Tung Jul 29 '14 at 02:54
  • It will help others if you write an answer and accept it. – philipxy Jul 29 '14 at 02:55

1 Answers1

1

When the login is set as 'sysadmin', the default schema will be fixed as dbo and cannot be changed whatsoever.

Tony Trinh Duy Tung
  • 21
  • 5