Avoiding table's owner with table's name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Avoiding table's owner with table's name

Author

Topic

noamhait
Starting Member

1 Post

Posted - 2013-02-05 : 08:16:29

How do I configure the server not to ask for table's owner with table's name in queries?

Thanks
Noam

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 10:06:03

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 10:06:09

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 10:13:58

ah...didnt refresh before i post the reply.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources