SQL server schema and default schema

sql-server sql-server-2005 tsql schema
pdiddy picture pdiddy · Sep 27, 2010 · Viewed 76.1k times · Source

I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema ... SELECT * FROM [myschema].table

I set the default schema for my user using management studio and also ran the ALTER USER myUser WITH DEFAULT_SCHEMA [myschema] and I still get the invalid object 'table' when writing a query without the schema (SELECT * FROM table)

Is there a way to write SELECT * FROM table without having to specify the schema name all the time?

It's on SQL 2005 using SQL Management Studio.

Answer

Dustin Laine picture Dustin Laine · Sep 27, 2010

Is the user an SA, if so it will not work, according to the documentation SA users are always defaulted to the dbo schema.

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More