Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


unable to access the Table without schema


unable to access the Table without schema

Author
Message
sharath.chalamgari
sharath.chalamgari
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 798
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?
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
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/
sharath.chalamgari
sharath.chalamgari
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 798
My Question is why it is not working when i write a Query like

select * From tableA

as my default schema is Risk.
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
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/
sharath.chalamgari
sharath.chalamgari
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 798
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.
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
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/
sharath.chalamgari
sharath.chalamgari
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 798
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.
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
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/
Luiz-458831
Luiz-458831
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 646
You must remove 'sysadmin' server role from the login you use
sirajamscse
sirajamscse
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search