Schema

Question

Schema

padmakumark26

Mr or Mrs. 500

Points: 513
More actions
December 8, 2016 at 6:18 am

#316620

I created a user (User1) and Schema (shema1). Made schema1 as defult user schema.
Now i cannot access table name without schema1.table name. Select * from table don't work

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

padmakumark26 (12/8/2016)
I created a user (User1) and Schema (shema1). Made schema1 as defult user schema.
Now i cannot access table name without schema1.table name. Select * from table don't work

I see no problem here... just add the schema. What is your question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

padmakumark26 Mr or Mrs. 500 Points: 513 More actions · Answer 2

With user which have default schema we can access object without specifying schema name right ?

Like select * from table .

But i cannot access like that it is giving error invalid object name. I need to do like Select * from schema.table but i need like select * from table

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

If you made schema1 the default schema for user1, that'll only work for user1, not for you as well, unless your own default schema also happens to be schema1.

John

Phil Parkin SSC Guru Points: 246991 More actions · Answer 4

padmakumark26 (12/8/2016)
With user which have default schema we can access object without specifying schema name right ?
Like select * from table .
But i cannot access like that it is giving error invalid object name. I need to do like Select * from schema.table but i need like select * from table

Select ... from table

without providing schema name is bad practice, because it can result in ambiguity and cache-miss events. Apart from laziness, do you have any justification for not wanting to schema-qualify your table names in queries?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

padmakumark26 Mr or Mrs. 500 Points: 513 More actions · Answer 5

We are combining two db into one .Now both have default schema as dbo.

In the New combined DB we will have two user(Hms,EAM) and two schema (HMS,EAM) and login (euser,Huser)

We're having issues now that we have restored the backups from the shared host to our new box. When logged in as 'euser', we need the queries to run like this:

SELECT * FROM vw_EmployeesStuff

And not have to query like:

SELECT * FROM HMS.vw_EmployeesStuff

We've tried to change the default schema for the user from dbo to hms, but it doesn't seem to be 'taking'. Any thoughts or help is much appreciated.

Phil Parkin SSC Guru Points: 246991 More actions · Answer 6

... we need the queries to run like this:

You still haven't explained why.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 7

Inside those views are they maybe referencing things with schema specified, ie SELECT <columns> FROM dbo.TABLE in the views in both schemas?

drew.allen SSC Guru Points: 76998 More actions · Answer 8

padmakumark26 (12/8/2016)
We are combining two db into one .Now both have default schema as dbo.
In the New combined DB we will have two user(Hms,EAM) and two schema (HMS,EAM) and login (euser,Huser)
We're having issues now that we have restored the backups from the shared host to our new box. When logged in as 'euser', we need the queries to run like this:
SELECT * FROM vw_EmployeesStuff
And not have to query like:
SELECT * FROM HMS.vw_EmployeesStuff
We've tried to change the default schema for the user from dbo to hms, but it doesn't seem to be 'taking'. Any thoughts or help is much appreciated.

You didn't mention whether both schemas contain the view vw_EmployeeStuff. If they do, you will have to specify the schema. If they don't, you can look into creating the synonym eam.vw_EmployeeStuff to point to hms.vw_EmployeeStuff.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 9

ZZartin (12/8/2016)
Inside those views are they maybe referencing things with schema specified, ie SELECT <columns> FROM dbo.TABLE in the views in both schemas?

Quoting ZZartin to bring more attention to his response. I'm betting this is part of the problem.

Can you confirm it does work when you qualify the schema? I wasn't certain that you confirmed the objects do exist in the new schema.

Is it working for one user and not the other or are they both not working?

Check the user default schema here:SELECT DatabasePrincipalName = dp.name,

dp.default_schema_name

FROM sys.database_principals AS dp

You may want to verify that they have permission to select all the objects. Setting a default schema is not the same as owning the schema.

You can check schema owners here:

SELECT SchemaName = s.name,

DatabasePrincipalName = dp.name

FROM sys.schemas AS s

INNER JOIN sys.database_principals AS dp

ON s.principal_id = dp.principal_id

AND dp.is_fixed_role = 0;

As others have said, you should always qualify your schema. Depending on the default user schema leads to all kinds of problems. Hopefully that is just a temporary fix for you and you'll update your SQL with schema names. 😉

Wes
(A solid design is always preferable to a creative workaround)