December 8, 2016 at 6:18 am
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
December 8, 2016 at 6:38 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 8, 2016 at 6:51 am
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
December 8, 2016 at 7:01 am
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
December 8, 2016 at 7:15 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 8, 2016 at 10:48 am
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.
December 8, 2016 at 11:03 am
... we need the queries to run like this:
You still haven't explained why.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 8, 2016 at 11:03 am
Inside those views are they maybe referencing things with schema specified, ie SELECT <columns> FROM dbo.TABLE in the views in both schemas?
December 8, 2016 at 11:33 am
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
December 9, 2016 at 1:17 pm
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)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply