sql server odbc: Hi,

under SQL Server 2005 I greatet a user with:

USE MYDB
CREATE USER TEST FOR LOGIN TEST WITH DEFAULT_SCHEMA = TEST
ALTER LOGIN TEST WITH DEFAULT_DATABASE =MYDB

and schema:

CREATE SCHEMA TEST AUTHORIZATION TEST;

Connecting to the database using SQL Native Client (2005.9.1399.0) and ODBC
with the user "TEST" leads to default schema "DBO".

That means: Selecting a table using the unqualified name:

SELECT * FROM MYTABLE

leads to "objekt not found" error, if the table "MYTABLE" is part of the
schema "TEST"

after executing the statement

EXECUTE AS USER='TEST'

the statement

SELECT * FROM MYTABLE

succeeds.

Why the default schema is not initially set to "TEST" after connection?


Greetings

Michael

Hi, Michael,
I understand that you would like to know why the default schema was not
initially set to "TEST" after you log on your SQL Server with the login
TEST.
If I have misunderstood, please let me know.

Unfortunately per my test in SQL Server Management Studio, I could not
reproduce your issue. My test process was as following:
1. Logged on my SQL Server instance with a sysadmin account and created a
login named "globaltest" with default master database;
2. Executed the statement:
USE TestDB
CREATE USER TEST FOR LOGIN globaltest WITH DEFAULT_SCHEMA = TEST
ALTER LOGIN globaltest WITH DEFAULT_DATABASE TestDB
3. Executed the statement:
CREATE SCHEMA TEST AUTHORIZATION TEST;
4. Changed a table dbo.T1 to TEST.T1;
5. Closed the current connection and connected to the SQL Server instance
with the login 'globaltest';
6. Executed the query:
SELECT * FROM T1;
Everything worked fine. Are there some differences between our tests?

I recommend that you run "SELECT CURRENT_USER" to check if the user TEST is
the current user after you log on your SQL Server with the TEST login. You
may also perform a test according to my steps to see if the issue occurs.

If you have any other questions or concerns, please feel free to let me
know.

Have a good day!

Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Hi, Charles,

thanks for the quick reply.

To this post I added the file "init-db.sql" (hope it arrives). The script
can be used to create a little test-database. You can change the values of
the variables at the beginning if you want. I used "sqlcmd" to run the
script.

I don't know if you used the "SQL Native Client" and ODBC to connect to the
database? But I testet a connection to the database with "SQL Server
Management Studio" too and I got the same results (a "SELECT * FROM T1"
after connect with user/login "TEST" fails).

I even tried to "SELECT CURRENT_USER" after the connect (as you recommended)
and it returned "dbo".
That's what I expected, because it explains the fact that the "SELECT"
statement fails.

But why? I think the "CURRENT_USER" after connect should be "TEST" not
"dbo". Is there anything wrong in the script "init-db.sql"?

Thanks for your help

Greetings

Michael

--
"Charles Wang[MSFT]" <changliw@online.microsoft.com> schrieb im Newsbeitrag
news:O4KnVyuWHHA.2352@TK2MSFTNGHUB02.phx.gbl...
[quoted text, click to view]


begin 666 init-db.sql
M.E)%4T54#0HZ4T545D%2($1"04Y!32!415-41$(-"CI3151605(@1$)!4U12
M("=415-41$(G#0HZ4T545D%2($1"04Q/1R G5$535$1"3$]')PT*.E-%5%9!
M4B!$0D9)3$4@)T,Z7%1%4U1$0BY-1$8G#0HZ4T545D%2($1"3$]'1B G0SI<
M5$535$1"3$]'+DQ$1B<-"CI3151605(@55-24U12("=415-4)PT*.E-%5%9!
M4B!54U).04T@5$535 T*#0HM+2 M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM#0HM+2!697)B:6YD=6YG('IU
M<B!$871E;F)A;FL@:&5R<W1E;&QE;@T*+2T@+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+0T*55-%($U!4U1%
M4CL-"D=/#0H-"@T*+2T@+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+0T*+2T@06YM96QD96YA;64@(E1%4U0B
M(&5R>F5U9V5N+"!W96YN(&YO8V@@;FEC:'0@=F]R:&%N9&5N#0HM+2 M+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM#0I)1B!.3U0@15A)4U13("A314Q%0U0@*B!&4D]-('-Y<RYS97)V97)?
M<')I;F-I<&%L<R!72$5212!N86UE(#T@3B0H55-24U12*2D@0D5'24X-"@T*
M"4-214%412!,3T=)3B D*%534DY!32D@5TE42"!005-35T]21" ]($XD*%53
M4E-44BDL($1%1D%53%1?1$%404)!4T4@/2!-05-415(L#0H)1$5&055,5%],
M04Y'54%'12 ]($1%55130T@L($-(14-+7T584$E2051)3TX@/2!/1D8L($-(
M14-+7U!/3$E#62 ]($]&1CL-"@T*"45814,@<WES+G-P7V%D9'-R=G)O;&5M
M96UB97(@0&QO9VEN86UE(#T@3B0H55-24U12*2P-"@E <F]L96YA;64@/2!.
M)V1B8W)E871O<B<[#0H)15A%0R!S>7,N<W!?861D<W)V<F]L96UE;6)E<B!
M;&]G:6YA;64@/2!.)"A54U)35%(I+ T*"4!R;VQE;F%M92 ]($XG<WES861M
M:6XG.PT*#0H)04Q415(@3$]'24X@)"A54U).04TI($5.04),13L-"@T*14Y$
M#0I'3PT*#0HM+2 M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM#0HM+2!$871E;F)A;FL@9V=F+B!L]G-C:&5N
M#0HM+2 M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM#0I)1B!%6$E35%,@*%-%3$5#5"!N86UE($923TT@<WES
M+F1A=&%B87-E<R!72$5212!N86UE(#T@3B0H1$)!4U12*2D@0D5'24X-"@E$
M4D]0($1!5$%"05-%("0H1$)!3D%-*3L-"D5.1 T*1T\-"@T*+2T@+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+0T*+2T@1&%T96YB86YK(&YE=2!E<G-T96QL96X-"BTM("TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2T-"D-2
M14%412!$051!0D%312 D*$1"04Y!32D@3TX@4%))34%262 H#0H-"@E.04U%
M(#T@3B0H1$)!4U12*2P@1DE,14Y!344@/2!.)"A$0D9)3$4I+"!325I%(#T@
M-# Y-DM"+ T*"4U!6%-)6D4@/2!53DQ)34E4140L($9)3$5'4D]75$@@/2 Q
M,#(T2T(-"@T**2!,3T<@3TX@* T*#0H)3D%-12 ]($XD*$1"04Q/1RDL($9)
M3$5.04U%(#T@3B0H1$),3T=&*2P@4TE:12 ](#$P,C1+0BP-"@E-05A325I%
M(#T@,C T.$="+"!&24Q%1U)/5U1((#T@,3 E#0H-"BD@0T],3$%412!,871I
M;C%?1V5N97)A;%]#25]!4SL-"D=/#0I%6$5#(&1B;RYS<%]D8F-M<'1L979E

Hi, Michael,
Thanks for your posting your script file here.

I reproduced your issue at my side. After carefully checked the script, I
found that the issue was caused by the sysadmin role assigned to the login.
If a login has the server role sysadmin, it will be assigned to dbo
automatically.
That is why you got dbo when you executed the statement "SELECT
CURRENT_USER".
If the sysadmin role is cancelled, the issue will not occur.

Please refer to:
Database Owner (dbo)
http://msdn2.microsoft.com/en-us/library/aa905208(sql.80).aspx

Hope this helps. Please feel free to let me know if you have any other
questions or concerns.

Have a good day!

Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Hi Charles,

many thanks for your help. In the execution environment it's not required
that the login is assigned to the sysadmin role.
So i'll change the skript!

Greetings

Michael

Hi, Michael,
Thanks for your updating and response.

I am very glad to hear that the issue has been resolved. Please feel free
to let me know if you have any other questions or concerns.

Have a good day!

Charles Wang
Microsoft Online Community Support