August 17, 2009 at 6:22 am
hi
i hav a a schema called user
if i login with sa i hav to give user
eg select * from user.table
i dnt want that i want i can directly access the table....
when login with sa ...
eg select * from table
using sqlserver 2005
August 17, 2009 at 6:45 am
Using the schema name in a query is preferred. It helps the system determine what table/view/procedure/etc is being accessed or executed without having to go through default schemas and such.
You should get used to using two part naming suchs as:
select UserName from user.table where UesrID = 1;
It makes your code easier to read, and explicitly defines which table you are accessing. If you had two tables, user.table and dbo.table, which table is correct for this code:
select * from table
It could be either and depending on context, one could be the correct table and the other the wrong table.
August 17, 2009 at 7:15 am
Thankz lynn but i want to know bcoz i hav read some were that we can do that without specifying the schema ... can u help me in this
August 17, 2009 at 7:41 am
Find the document then!! 😉
Seriously though, Lynn is right - the query should reference the correct schema. Using the sa login will default to the dbo schema. I tried a few things to see if I could change this just now and I could not alter it.
Interesting to know if there is a way, but I'm not sure why you'd want to do that in the first place?
A bit off topic, but using "sa" as a login should not really be happening! Could you not create a login with adequate permissions which defaults to the user schema?
August 17, 2009 at 11:08 am
samsql (8/17/2009)
Thankz lynn but i want to know bcoz i hav read some were that we can do that without specifying the schema ... can u help me in this
Just because you CAN do something doesn't mean you SHOULD do something. In this particular case, what you should do, specifying the schema, is what you really should be doing.
August 17, 2009 at 1:47 pm
samsql (8/17/2009)
Thankz lynn but i want to know bcoz i hav read some were that we can do that without specifying the schema ... can u help me in this
If you do not specify the schema - you can get multiple execution plans stored for the same query. One for each user, even when the table only exists in the dbo schema.
You can avoid the above by defining the default schema for each user to be dbo - however, that is just a workaround and can be avoided completely by specifying the schema in your queries.
Besides, there are situations where you cannot specify a default schema for the user - and in those situations you will end up with multiple execution plans. For example, if you are using a Windows Group you can't defined the default schema for the group.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2009 at 1:53 pm
Why don't you simply create a synonym.
create synonym table_name for user.table_name
select * from table_name
August 17, 2009 at 2:02 pm
andersonrj18 (8/17/2009)
Why don't you simply create a synonym.create synonym table_name for user.table_name
select * from table_name
Even with a synonym - you are going to have to same issues. The recommendation is to schema qualify objects, which is not really that hard to do.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply