Hi guys, please help.
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?
Yeah, it's a good habit to write the full name of the Table with Schema
name as prefix, however, we did our development in SQL 2000 and trying
to upgrade to SQL2005, so trying to avoid scanning codes and inserting
in thousand of places.
How could we omit the Schema name when referrring to Tables? Thanks.
ps. I posted in another topic days ago, but nobody is replying, when I
tried to bring it forward by replying myself, it failed. please help me
, thanks! 3 15321
Because when you do not specify a schema, SQL Server first looks for the
object name under a schema which matches your login (kkhad\kimliu). When it
doesn't find an object matching that name under your schema, then it looks
in the dbo schema. If it doesn't find it there, it throws the error message
you are seeing.
--
Mike
Mentor
Solid Quality Learning http://www.solidqualitylearning.com
"athos" <at*******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com... Hi guys, please help.
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?
Yeah, it's a good habit to write the full name of the Table with Schema name as prefix, however, we did our development in SQL 2000 and trying to upgrade to SQL2005, so trying to avoid scanning codes and inserting in thousand of places.
How could we omit the Schema name when referrring to Tables? Thanks.
ps. I posted in another topic days ago, but nobody is replying, when I tried to bring it forward by replying myself, it failed. please help me , thanks!
athos schrieb: Hi guys, please help.
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?
Please refer to the BOL on CREATE USER / ALTER USER.
There are some restrictions on how DEFAULT_SCHEMA can be used: http://msdn2.microsoft.com/en-US/ms173463.aspx
hth
Stefka
athos (at*******@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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: N. Shamsundar |
last post by:
In DB2 V8.1.5, if one uses SET SCHEMA to a schema other than the default
schema, and subsequently issues a LIST TABLES command, it is still the
tables in the default schema that are shown. Is this...
|
by: Robert Stearns |
last post by:
I ran the following bit of SQL and my PRIMARY KEY wound up in schema
SYSIBM called SQL.... not schema is3 called primary. The index
registation did wind up there. Obviously there's something I...
|
by: dave71 |
last post by:
Hi
Could someone please advise me how to remove the schema name from
linked tables within Access. For example when I connect to a Oracle
database via Microsoft ODCB for Oracle the list of...
|
by: Bob Stearns |
last post by:
The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?
|
by: sastry.m.s |
last post by:
Hi,
I have tables created in my DB2 database created under a schema. Now i
want to get connected to these tables with a username different from
this schema name from my java application. When...
|
by: Eric |
last post by:
Help! I created a XML schema with a Visual Studio tools. I'm filling a
dataset with a DataAdapter. Before I use the "WriteXml" method to write the
data to a xml file, I want to map the XSD file I...
|
by: David++ |
last post by:
Dear List,
Is it possible to have a schema with tables of the same name? I need to
build an XML document based on the following schema -
<?xml version="1.0" encoding="utf-8"?>
<xs:schema...
|
by: %NAME% |
last post by:
Suppose I am an admin of a database instance. Now
I need to specify that user A has the right to create tables
in his own schema, but not anywhere else.
In order to let user A create tables, i...
|
by: karanbikash |
last post by:
Hi ,
I would like to know :
1.how to find all the schema in my DB2 database on Main frame
machine .
2. How to see all the tables for a given schema .
3. What are the default schema in Db2 on...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |