Beginner Question - SQL Server Forums

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 Administration

Beginner Question

Author

Topic

OldTimer
Starting Member

8 Posts

Posted - 2011-07-29 : 23:34:32

As you'll detect from my questions, I'm very new to SQL Server.

In the SQL Server 2008 Management Studio, the object explorer on the left side. If I expand a database then open the Tables folder I will see a list of tables in that database. There is a prefix to each table name in the tree (such as dbo.tablename). What is that prefix?

I am seeing a database that has several tables - each of the tables is in the db twice, once as dbo.table and again as link.table. Why might two tables with the same name be in the database?

If the database is queried and the prefix (whatever that is) is not specified, how would it decide which table to query against? (I believe that this is a problem with an application I am trying to support.

Thanks in advance for any insight into this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 01:37:45

quote:
Originally posted by OldTimer

As you'll detect from my questions, I'm very new to SQL Server.

In the SQL Server 2008 Management Studio, the object explorer on the left side. If I expand a database then open the Tables folder I will see a list of tables in that database. There is a prefix to each table name in the tree (such as dbo.tablename). What is that prefix?
that prefix denotes schema to which table belongs. Tables are created inside schema. there can be same tables existing within multiple schema
I am seeing a database that has several tables - each of the tables is in the db twice, once as dbo.table and again as link.table. Why might two tables with the same name be in the database?
as explained above they are in different schema so can co exist in same db. when you access tables by only their name it depends on what your default schema is and table in that schema gets accessed. if you want to specifically get details from one of the tables always use schema.table format
If the database is queried and the prefix (whatever that is) is not specified, how would it decide which table to query against? (I believe that this is a problem with an application I am trying to support.
same as above
Thanks in advance for any insight into this.

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

OldTimer
Starting Member

8 Posts

Posted - 2011-08-01 : 11:16:06

I think that I now can supply enough information that someone might be able to help me.

The database is called link, the user is called link, the user's default schema is link.
In the database there is a table: dbo.Events and another called link.Events

If I select from Events without specifying a schema, it selects from dbo.Events.
If I select from link.Events it selects records just fine.

If I rename the dbo.Events table, I can still select from flink.Events, however, if I try to select from Events I get an error message: "Invalid object name 'Events'"

What is wrong that it can select from link.Events but not Events if the default schema is link?

Again, thanks for any suggestions you can provide.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 12:53:46

is user mapped to dbo role in db?

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

OldTimer
Starting Member

8 Posts

Posted - 2011-08-01 : 15:08:09

I don't see a dbo role. I see a dbo user and a dbo schema but no dbo role. I looked both under database roles and server roles.

The only role that I see checked for the link user is db_owner. link owns the link schema but sa owns the link database.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-01 : 15:39:30

You say the users default schema is link.

And you say that YOU can select...

Is YOUR default schema dbo or link?

Your application issue is easily resolved by specifying schema.

OldTimer
Starting Member

8 Posts

Posted - 2011-08-01 : 15:49:43

Poorly stated on my part, sorry.

I have no account in the database. I am using the link account. The link account's default schema is link.

The database is called link, the user is called link, link's default schema is link.

In the database there is a table: dbo.Events and another called link.Events
Using the link account, if I select from Events without specifying a schema, it selects from dbo.Events.
If link selects from link.Events it selects records just fine.

If I rename the dbo.Events table, I can still select from link.Events, however, if I try to select from Events (with no schema identifier) I get an error message: "Invalid object name 'Events'"

I am not able to change the application to get around the problem. It seems to me that there must be some sort of permissions issue, that it can select from link.Events but not Events (without the schema identifier) despite link being the users default schema.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-01 : 15:56:58

Is the owner of the schema dbo or link? I expect it to be owned by dbo - and that would be why it doesn't see the object in the link schema.

Either way, you should get used to schema qualifying all objects. There are performance benefits from doing so, over and above the lookup to the right schema. When you do not schema qualify the objects, a plan will be generated for every user that accesses that object. For example:

SELECT ... FROM Events;

If called by the user 'link' and the user 'joe', there will be two plans generated unless both users default schema is the same.

SELECT ... FROM link.Events;

The above will always generate a single plan for the link schema, regardless of the user's default schema.

Jeff

OldTimer
Starting Member

8 Posts

Posted - 2011-08-01 : 16:54:11

The schema (link) is owned by the link user.

I understand what you are saying about using the schema qualifier. For this one, however, I'm sorta desperate to get the problem solved.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-01 : 18:45:40

What does this return?

use link;
GO

SELECT	s.name [schema name], p.name [principal name], p.default_schema_name
FROM	sys.database_principals p
JOIN	sys.schemas s
On	s.principal_id = p.principal_id
WHERE	s.name = 'link'

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-01 : 22:48:59

If the schema is owned by the link user - it will not look to the dbo schema for the object when it is not schema qualified. You can either change the owner of the schema to dbo or you need to schema qualify the query.

I would recommend doing both - but, it really depends on why you need the link schema owned by the link user. I always create my schema's and have them owned by dbo, that way I can maintain the ownership chain across schemas.

Jeff

OldTimer
Starting Member

8 Posts

Posted - 2011-08-02 : 08:47:53

quote:

Originally posted by russell

What does this return?

use link;
GO

SELECT	s.name [schema name], p.name [principal name], p.default_schema_name
FROM	sys.database_principals p
JOIN	sys.schemas s
On	s.principal_id = p.principal_id
WHERE	s.name = 'link'

schema name: link
principal name: link
default_schema_name: link

If I connect as link,
Select * from link.Events returns records
Select * from Events returns invalid object name 'Events'

What could be wrong with the configuration of this database to cause this?

OldTimer
Starting Member

8 Posts

Posted - 2011-08-02 : 11:16:10

I think I have discovered an explanation for this behavior.

The login associated with the link user had been granted the sysadmin role. When I removed that role, the behavior changed so that Select * from Events selects from link.Events, not dbo.Events.

Does this seem plausible. Is this the way SQL Server should treat that login?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-02 : 11:45:31

Yep, looks like you nailed it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 11:51:58

quote:
Originally posted by OldTimer

I think I have discovered an explanation for this behavior.

The login associated with the link user had been granted the sysadmin role. When I removed that role, the behavior changed so that Select * from Events selects from link.Events, not dbo.Events.

Does this seem plausible. Is this the way SQL Server should treat that login?

ok. that explains it. thats why links default schema was changed to dbo.

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

OldTimer
Starting Member

8 Posts

Posted - 2011-08-02 : 14:09:49

I know that thank you's are frowned upon on some discussion boards, though I'm not certain about here. I am very grateful to all of you for your help in educating me and solving this problem. I've recently been thrust into SQL Server after years of working with Oracle. I hope, someday, to be up to speed on SQL Server.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-02 : 14:33:17

You're well on your way. Post back with any questions you run into. And the thank yous are welcome here.

We get to run up our post count by saying you're welcome lol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources