Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

unable to access the Table without schema Expand / Collapse
Author
Message
Posted Monday, January 31, 2011 2:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:33 AM
Points: 1,128, Visits: 756
i have a schema called Risk and having some tables under it.

even through my user default scema is set to risk, i am unable to access the Table

i need to write a Query like

select * From Risk.tableA

and i am unable to use short cut Alt + F1 in this case.
How to solv this Problem.

is it having my DB name also as a RISK could be a Problem?

Post #1056031
Posted Monday, January 31, 2011 2:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
You have to use the schema’s name and put apostrophes around it (e.g. ‘Risk.TableA’). If it really bothers you, you can try writing your own procedure that will be a rapper for sp_help. If you’ll use only the object’s name without the schema’s name, it will add it and then call sp_help with the correct parameter. You can set a keyboard shortcut to call your own procedure.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1056041
Posted Monday, January 31, 2011 2:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:33 AM
Points: 1,128, Visits: 756
My Question is why it is not working when i write a Query like

select * From tableA

as my default schema is Risk.

Post #1056045
Posted Monday, January 31, 2011 3:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
Are you sure that this is the default schema? Can you check if this is the really the user’s default schema with this query?
select default_schema_name
from sys.database_principals
where name = 'WriteUserNameHere'

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1056050
Posted Monday, January 31, 2011 3:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:33 AM
Points: 1,128, Visits: 756
Adi Cohn-120898 (1/31/2011)
Are you sure that this is the default schema? Can you check if this is the really the user’s default schema with this query?
select default_schema_name
from sys.database_principals
where name = 'WriteUserNameHere'

Adi


Yes, the above Query in my case is giving me the result as "RISK" which is the schema i set it to.

i am unable to find where i did wrong.
Post #1056057
Posted Monday, January 31, 2011 4:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
It should work. I’ve just tested it with the script bellow. Do you have the problem when you run the select statement from a SSMS window or do you have it inside a procedure? Could it be that you have execute as statement somewhere in the code?

--Creating a demo database and a demo login
create database Demo
go

create login Demo1 with password = '1qaz@WSX3edc'
go

--Creating a schema and 2 tables on on the new schema and one in the dbo schema
use Demo
go

create schema test
go

create table dbo.tbl (i int)
go
create table test.tbl2 (i int)
go

--Creating a user without specifying his default schema
--which makes the dbo his default schema
create user Demo1 from login Demo1
go

exec sp_addrolemember db_datareader, Demo1
go

--Executing a query as user Demo1. Since I didn't
--specify the schema's name, the server will
--use the default schema
execute as user = 'Demo1'
select * from tbl
revert
go

--Modifying the user's default schema.
alter user Demo1 with default_schema = test

--This time the schema test will be used
execute as user = 'Demo1'
select * from tbl2
revert
go

--cleanup
use master
go
drop database Demo
go
drop login Demo1

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1056069
Posted Monday, January 31, 2011 4:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:33 AM
Points: 1,128, Visits: 756
Adi Cohn-120898 (1/31/2011)
It should work. I’ve just tested it with the script bellow. Do you have the problem when you run the select statement from a SSMS window or do you have it inside a procedure? Could it be that you have execute as statement somewhere in the code?

--Creating a demo database and a demo login
create database Demo
go

create login Demo1 with password = '1qaz@WSX3edc'
go

--Creating a schema and 2 tables on on the new schema and one in the dbo schema
use Demo
go

create schema test
go

create table dbo.tbl (i int)
go
create table test.tbl2 (i int)
go

--Creating a user without specifying his default schema
--which makes the dbo his default schema
create user Demo1 from login Demo1
go

exec sp_addrolemember db_datareader, Demo1
go

--Executing a query as user Demo1. Since I didn't
--specify the schema's name, the server will
--use the default schema
execute as user = 'Demo1'
select * from tbl
revert
go

--Modifying the user's default schema.
alter user Demo1 with default_schema = test

--This time the schema test will be used
execute as user = 'Demo1'
select * from tbl2
revert
go

--cleanup
use master
go
drop database Demo
go
drop login Demo1

Adi


Thanks adi for showing help on my issue.

i am facing Problem in SSMS it self.
on Friday i was able to access the tables without the schema,but today i couldn't able to access it.

i am not sure what dba has done to my login.i have just asked him to Provide Rights to Run the Profiler.
(GRANT ALTER TRACE TO [username])
which was not there before. Now i came up with this issue.

do i need to check any thing other than this default schema.



Post #1056084
Posted Monday, January 31, 2011 6:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:39 AM
Points: 2,104, Visits: 5,378
As far as I know the only things that you should check is that the table exists on your default schema and that you have permissions to work with this table.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1056124
Posted Wednesday, May 18, 2011 1:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:02 AM
Points: 74, Visits: 486
You must remove 'sysadmin' server role from the login you use
Post #1111296
Posted Saturday, November 02, 2013 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 02, 2013 7:39 AM
Points: 1, Visits: 0
@Luiz-458831

Fantastic catch ..I was keep on install and reinstall of sql express and sql management studio.because
My DB contains table and some too from schema.
Same problem..i cant execute query without prefix schema.so i like to go security,logins option and change most time DB Goes inaccessible,I dont know how to get that for that login.what i did in my scenario.
My DB name: CustomerDB
Schema: Customer ,User:Customer,Pswd:*****
1.i attached DB to management studio
2.User folder of CustomerDB contain Customer(user)
3.Mapping schema on both places security ->Login and Security user
4.After that i missed something which is most improtant in my case to avoid schema name.
[color=#d4d4d4]Final note[/color]
That is Uncheck sysadmin in server role..That it..now i run queries without schema..There is no proper references for this case

Thanks once again
Post #1510854
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse