SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Avoiding table's owner with table's name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

noamhait
Starting Member

1 Posts

Posted - 02/05/2013 :  08:16:29  Show Profile  Reply with Quote
How do I configure the server not to ask for table's owner with table's name in queries?

Thanks
Noam

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 02/05/2013 :  10:06:03  Show Profile  Reply with Quote
If the table is not in the default schema of the user, or not in the dbo schema, you have to specify the schema. For some type of objects - UDFs - you must specify the schema regardless.

What I suspect may be happening to you is that the default schema for the users may be something different from dbo. In lot of cases it is useful and you do want that. However, especially in the case of windows authenticated users, the default schema becomes the same as the username. Changing their default schema to dbo may be what are really looking for. You can change a user's default schema in the properties dialog under the DatabaseName->Security -> Users in the SSMS Object Explorer.

Changing the default schema would affect only objects created going forward. You can change the schema of existing objects if you need to using
-- in this example, YourDomain\YourUser is a windows authenticated user
ALTER SCHEMA dboTRANSFER [YourDomain\YourUser].Tablename;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/05/2013 :  10:06:09  Show Profile  Reply with Quote
you need make the schema your default schema. then you dont need to schema qualify the table names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/05/2013 :  10:13:58  Show Profile  Reply with Quote
ah...didnt refresh before i post the reply.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000