Posted by Erland Sommarskog on 01/10/06 00:55

athos (athos.liu@gmail.com) writes:
> What I did is :
> Step1. create a Database named [Test], it's default owner is [sa]
> Step2. create a User in Database [Test] named [kimliuTest], which maps
> to the Login [kkhad\kimliu] on this Database Server, it's a windows
> domain user.
> Step3. create a Schema in Database [Test] named [schTest], whose Schema
> Owner is User [kimliuTest]
> Step4. created two tables: [dbo].[t1] and [schTest].[t2]
> Step5. run the script
>
> ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
>
> to set the default schema.
>
> Now my problem is, when I try to run
>
> select * from schTest.t2
>
> , everything is OK, but if I run
>
> select * from t2
>
> the error message says:
>
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 't2'.
>
> why? I'm loggin in with my [kkhad\kimliu] windows domain id , Active
> Monitor also shows that. but the command does not work?

It should work. See the script below. I create a user without login
to make the script simpler, but it should not matter.

What I think may be the mistake is that you never changed context
to the test user, so you were still dbo when you ran the SELECT.
If you comment out the EXECUTE AS in the script, you will see this
effect.



EXECUTE AS LOGIN = 'sa'
go
CREATE DATABASE athos
go
REVERT
go
USE athos
go
CREATE USER test FROM LOGIN [KESÄMETSÄ\sommar]
-- change to your name here.
GRANT CONTROL TO test
go
CREATE SCHEMA schtest
go
CREATE TABLE dbo.t1 (a int NOT NULL)
CREATE TABLE schtest.t2 (b int NOT NULL)
go
EXECUTE AS user = 'test'
go
ALTER USER test WITH DEFAULT_SCHEMA = schtest
go
SELECT * FROM t1
go
SELECT * FROM t2
go
REVERT
go
USE master
DROP DATABASE athos



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]