setting current schema in SQL server

_{Friday, October 17, 2008 4:46 AM}

Hi,

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

Thanks

_{Monday, November 3, 2008 2:03 PM ✅Answered}

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

_{Friday, October 17, 2008 4:55 AM}

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 5:12 AM}

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:15 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

_{Sunday, October 19, 2008 12:07 PM}

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 3:47 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.

_{Friday, October 31, 2008 11:36 AM | 1 vote}

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.

_{Saturday, November 1, 2008 3:35 AM}

What grant statement?  I was referring to default schemas.  Could you post a sample of the code that isn't working?

_{Saturday, November 1, 2008 3:37 AM}

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.