Problem with default schema

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, 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, 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