January 31, 2011 at 2:16 am
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?
January 31, 2011 at 2:48 am
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/
January 31, 2011 at 2:59 am
My Question is why it is not working when i write a Query like
select * From tableA
as my default schema is Risk.
January 31, 2011 at 3:20 am
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/
January 31, 2011 at 3:45 am
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.
January 31, 2011 at 4:16 am
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/
January 31, 2011 at 4:55 am
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.
January 31, 2011 at 6:11 am
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/
May 18, 2011 at 1:15 pm
You must remove 'sysadmin' server role from the login you use
November 2, 2013 at 7:52 am
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.
Final note
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply