none
setting current schema in SQL server

    Question

  • Hi,

    Like in oracle 'alter system set current schema' do we have any alternative in SQL server ?

    Thanks
    Friday, October 17, 2008 4:46 AM

Answers

  • Hi,

    I don't think its possible to reset the default schema only for the duration of the EXEC AS. EXEC AS is provided only for the purpose of elevating security in certain cases

    1. By means of executing some code as a privileged user, so that other users need not be granted access on the database objects.
    2. Cross database access.

    In any case, even if you change the user, when a select statement is issued, SQL Server will look for the object first in the schema of the sp owner, which in your case is dbo and it can't check anywhere as that is the last level. Since the object is not available there, it fails. If this needs to be done, the only solution that I could think of is using a dynamic SQL.

    Cheers,
    Padmanabhan
    Monday, November 03, 2008 2:03 PM

All replies

  • Is this your meaning?

     

    Code Snippet

    execute as user = 'xxx'

    go

     

     

    This synax can only be supported in sql server 2005 or 2008.

     

    Friday, October 17, 2008 4:55 AM
    Moderator
  •  CN_SQL wrote:

    Is this your meaning?

     

    Code Snippet

    execute as user = 'xxx'

    go

     

     

    This synax can only be supported in sql server 2005 or 2008.

     



    Basically, i want to change the instance to which the user has to connect based on his ID.After he invokes the procedure based on his ID we set the schema he should refer to ; So,my though was to execute the command which will change him to use only the schema specified on which he should work on.

    Thanks
    Friday, October 17, 2008 5:12 AM
  • I think that you're looking to change the default schema for a user - correct?  So that all of the user's queries search for objects under their default schema first?  Try this:

    ALTER USER UserName WITH DEFAULT_SCHEMA = NewSchemaName
    Friday, October 17, 2008 5:15 AM
    Moderator
  • Hi,

    thanks for your help on that.

    I needed a clarification on one aspect.

    consider 2 users, user1 and user2; user1 maps to schema1 and user2 maps to schema2.

    user1(user2) invokes stored procedure sp1; inside the stored procedure depending on who invoked the procedure i will execute the statement
    alter user user1(user2) with default_schema = schema1(schema2).


    as an after effect of this is it that user1(user2) will be able to see only schema1's(schema2) objects ?



    Thanks in advance.

    Sunday, October 19, 2008 12:07 PM
  • Default schema does not define whether or not a user can see or even access objects from another schema, it simply defines where an unqualified query from that user resolves it's objects.  For instance, if User1 has default schema = schema1, then if User1 runs:

    SELECT * FROM SomeTable

    SQL Server will check the following for object matches:

    SELECT * FROM Schema1.SomeTable
    SELECT * FROM dbo.SomeTable

    If, however, that user were to specify a schema in their DML:

    SELECT * FROM Schema2.SomeTable

    Then SQL Server will use SomeTable in Schema2, provided that the user has sufficient authority to access Schema2.SomeTable.

    Does that make sense?  You're better off fully qualifying your DML than you are to try to switch schemas on a continuum - you will have far less administrative effort in the long run.  You also have the option to use EXECUTE AS to have a SQL User impersonate another user, with a different default schema, however this is predominantly used for security purposes, and not default schema assignment.

    Hope this helps.
    Sunday, October 19, 2008 3:47 PM
    Moderator
  • Hi,

    Thanks for the suggestion.

    However i tried calling the grant statement from the stored procedure.somehow it does not seem to have any effect .It does not seem to resolve the table names based on the schema that is set.

    Is there a procedure/way this can be invoked to have effect .If so how is it ?

    Thanks in advance.
    Friday, October 31, 2008 11:36 AM
  • What grant statement?  I was referring to default schemas.  Could you post a sample of the code that isn't working?

     

    Saturday, November 01, 2008 3:35 AM
    Moderator
  • Hi,

    Apologies for mentioning about grant it is 'alter schema'.here is what i did anyway

    I executed the following commands from the MS management express window in sql express 2005.

    grant create schema to varun
    go
    grant create table to varun;
    go

    execute as user = 'varun';
    go
    create schema schema1 authorization varun;
    go
    GRANT select,insert,delete,update ON SCHEMA :: schema1 TO varun1;

    create table schema1.t1(c1 int)
    go

    alter user varun1 with default_schema = schema1;

    execute as user = 'varun1';
    go
    insert into t1 values (1);
    select * from t1;

    result := 1

    I tried to execute in the same way in the procedure .
    Below procedure is invoked from the client and it is unable to resolve the table t1.

    What is the way to resolve the table name in the procedure. ?

    ALTER PROCEDURE [dbo].[executeTestProcedure]  
    AS
    BEGIN
        ALTER USER varun1 WITH DEFAULT_SCHEMA = schema1;
        execute as user = 'varun1';
        SELECT * from t1;
    END

    After executing this the error message is      " Invalid object name 't1'  " from the client.



    Suggestions are welcome.

    Thanks in advance.
    Saturday, November 01, 2008 3:37 AM
  • Hi,

    I don't think its possible to reset the default schema only for the duration of the EXEC AS. EXEC AS is provided only for the purpose of elevating security in certain cases

    1. By means of executing some code as a privileged user, so that other users need not be granted access on the database objects.
    2. Cross database access.

    In any case, even if you change the user, when a select statement is issued, SQL Server will look for the object first in the schema of the sp owner, which in your case is dbo and it can't check anywhere as that is the last level. Since the object is not available there, it fails. If this needs to be done, the only solution that I could think of is using a dynamic SQL.

    Cheers,
    Padmanabhan
    Monday, November 03, 2008 2:03 PM