user

  • 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

  • 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.

  • 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

  • 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?

  • 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.

  • 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

  • Why don't you simply create a synonym.

    create synonym table_name for user.table_name

    select * from table_name

  • 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