Dear All,
Our project was done in SQL 2000, now we are migrating to 2005.
however, there are lots of command such as:
a) SELECT * FROM myTableName
which, shall be
b) SELECT * FROM myLoginId.myTableName
both (a) and (b) are ok in sql 2000, as long as you login with
myLoginId, but, only (b) works in sql 2005.
seems in sql 2005, you can not omit the "Owner Name" when speifying a
table, unless the table's owner is 'DBO'.
which means, even if I login with myLoginId, I have to use the detailed
format (b),not (a).
this causes trouble in our project -- developed in sql 2000, hundreds
of command omitted the owner name prefix, troublesome to modify one by
one.
Any workaround or suggestions, please? Thanks.Hi, athos
In SQL Server 2005, schemas and users are different things (in SQL
Server 2000, they were about the same thing). You need to specify the
default schema for each user and then it will work without the prefix.
Use something like this:
ALTER USER myLoginId WITH DEFAULT_SCHEMA = myLoginId
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms190387.aspx
Razvan|||Hi Athos
In addition to Razvan's comments, please keep in mind that a login ID and a
database user are two different things. Only a database user can own objects
in a database; a login never does. Although the user name and login id might
be the same name, they are still two different things. And now in SQL 2005
we also have schema names and default schemas. If an object is in your
default schema, no matter who owns the object or the schema, you don't need
to specify the schema.
But it is good practice, and it has ALWAYS been good practice even in SQL
2000, to specify the owner name to avoid ambiguity. There is also a
performance benefit to fully qualifying the object as there is more chance
an existing query plan can be reused the object is fully qualified.
Please read about users and logins in the SQL 2000 BOL.
Please read about user/schema separation in the SQL 2005 BOL.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"athos" <athos.liu@.gmail.com> wrote in message
news:1136380087.559222.4320@.o13g2000cwo.googlegroups.com...
> Dear All,
> Our project was done in SQL 2000, now we are migrating to 2005.
> however, there are lots of command such as:
> a) SELECT * FROM myTableName
> which, shall be
> b) SELECT * FROM myLoginId.myTableName
> both (a) and (b) are ok in sql 2000, as long as you login with
> myLoginId, but, only (b) works in sql 2005.
> seems in sql 2005, you can not omit the "Owner Name" when speifying a
> table, unless the table's owner is 'DBO'.
> which means, even if I login with myLoginId, I have to use the detailed
> format (b),not (a).
> this causes trouble in our project -- developed in sql 2000, hundreds
> of command omitted the owner name prefix, troublesome to modify one by
> one.
> Any workaround or suggestions, please? Thanks.
>|||Hi guys,
thanks for your clarificatoin of my misunderstanding of some concepts.
yeah, it's good to fully specify the owner or schema name to avoid
ambiguity, however, it's me that suggest to upgrade our project from
SQL Server 2000 to 2005, and in our developing team I'm trying to avoid
other members' criticizing such as "why shall I search every line to
add some code due to your suggestion?" , or "why something runs fine in
SQL 2000 but not this modern 2005?"
back to my issue, let's say
--On my server "myServer" there is a login named "myLogin", and a
database "myDatabase".
--In "myDatabase" i have a user named "myUserFromMyLogin" which mapped
to the login "myLogin", and a table named "myTable" whose owner (user)
is "myUserFromMyLogin", and a schema named "mySchema" which has one
user "myUserFromMyLogin"
now, "If an object is in your default schema, no matter who owns the
object or the schema, you don't need
to specify the schema. " thanks for you help Kalen, however still a
little bit uncertain here, pls help...
first, when i loggin as the user "myUserFromMyLogin", I shall be using
its default schema "mySchema", right?
second, how to put the object, the table named "myTable", into the
default schema "mySchema"? I can see in the object explorer that the
table's full name is "myUserFromMyLogin.myTable", which means its owner
is "myUserFromMyLogin", which command shall I use to link the object
and the schema?
third, I can directly use commands like "SELECT * FROM myTable" now,
right?
thanks for your valuable time, pls help. Thanks.
yours,
athos.|||Hi, Athos
If the table's full name is "myUserFromMyLogin.myTable", this means
that it's in the "myUserFromMyLogin" schema. You only need to execute
the following (once), to make the "myUserFromMyLogin" schema to be the
default schema for the "myUserFromMyLogin" user:
ALTER USER myUserFromMyLogin WITH DEFAULT_SCHEMA = myUserFromMyLogin
Razvan|||Dear Razvan,
Thanks for your reply. Still cannot fix it.
First I made a mistake again. that is, the prefix of the table's
fullname is the schma, not the user.
OK, now 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:
Problem 1.
when I try to run
exec sp_changeobjectowner 'dbo.t1', 'schTest'
exec sp_changeobjectowner 'dbo.t1', 'kimliuTest'
, it returns
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'schTest' does not exist in
this database.
Msg 15411, Level 11, State 1, Procedure
sp_changeobjectowner, Line 107
Database principal or schema 'kimliuTest' does not exist in
this database.
why?
Problem 2:
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 help, thank you!
yours,
Athos.|||Hello, Athos
> Problem 1:
> when I try to run: exec sp_changeobjectowner [...]
> it returns:
> Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107
> Database principal or schema 'schTest' does not exist in this database.
Books Online 2005 says the following about sp_changeobjectowner:
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER SCHEMA or
ALTER AUTHORIZATION instead. sp_changeobjectowner changes both
the schema and the owner. To preserve compatibility with earlier
versions of SQL Server, this stored procedure will only change
object owners when both the current owner and the new owner
own schemas that have the same name as their database user names.
So that's why it didn't work: the schema name and the owner name were
different. We can use one of the following:
ALTER SCHEMA schTest TRANSFER dbo.t1
or:
ALTER AUTHORIZATION ON dbo.t1 to kimliuTest
Note that the two statements written above have different effects:
the first changes the schema, the second changes the owner.
You probably want to run:
ALTER SCHEMA schTest TRANSFER dbo.t1
ALTER AUTHORIZATION ON schTest.t1 TO SCHEMA OWNER
This will make the table to appear as if it was created as
"schTest.t1". Of course, it's better to create the table in the correct
schema from the start (instead of creating it in the dbo schema and
moving it to some other schema after that).
> Problem 2:
> when I try to run [...] select * from t2
> the error message says: "Invalid object name 't2'."
As I've said, you need to change the default schema for the user:
ALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTest
After running the above, if you run "select * from t2" it should work.
You need to keep in mind that if the schema "x" is owned by the user
"y", this does not imply that the user "y" has the schema "x" as his
default schema.
Razvan|||anybody could help please? thanks!|||anybody could help, please?|||anybody could help, please?|||anybody could help please?|||Dear Razvan,
Thanks for you explanation about [sp_changeobjectowner], [ALTER
SCHEMA..] and [ALTER AUTHORIZATION ..], however, the main issue is
still not solved.
Maybe there is some misunderstanding, what I did is:
Step1. create a Database named [Test] with default owner [sa]
Step2. in Database [Test], create a User [kimliuTest] mapping to Login
[kimliu] on this Database Server
Step3. in Database [Test], create a Schema [schTest] owned by
[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.
Repeat, I HAVE gone through Step 5, run the script "ALTER USER
kimliuTest WITH DEFAULT_SCHEMA = schTest " to set the default schema.
Then, after all of these steps --
Case 1: Of course, everything is OK when run "SELECT * FROM
schTest.t2", either by running in a SQL Query Editor Window or by .net
code.
Case 2: Cannot use "SELECT * FROM t2", the error message says: "Msg
208, Level 16, State 1, Line 1, Invalid object name 't2'. ".
Case 3: Strangely enough, everything is OK when run "EXECUTE AS
USER='kimliuTest'; SELECT * FROM schTest.t2".
Case 4: Everything is also OK when only run "EXECUTE AS LOGIN='kimliu'
".
Case 5: But, cannot use "EXECUTE AS LOGIN='kimliu'; SELECT * FROM t2",
the error message is the same: "Msg 208, Level 16, State 1, Line 1,
Invalid object name 't2'. ".
I am LOST! Anybody could help PLEASE~~ Thank you very much~
yours
Athos|||Dear Razvan,
Thanks for you explanation about [sp_changeobjectowner], [ALTER
SCHEMA..] and [ALTER AUTHORIZATION ..], however, the main issue is
still not solved.
Maybe there is some misunderstanding, what I did is:
Step1. create a Database named [Test] with default owner [sa]
Step2. in Database [Test], create a User [kimliuTest] mapping to Login
[kimliu] on this Database Server
Step3. in Database [Test], create a Schema [schTest] owned by
[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.
Repeat, I HAVE gone through Step 5, run the script "ALTER USER
kimliuTest WITH DEFAULT_SCHEMA = schTest " to set the default schema.
Then, after all of these steps --
Case 1: Of course, everything is OK when run "SELECT * FROM
schTest.t2", either by running in a SQL Query Editor Window or by .net
code.
Case 2: Cannot use "SELECT * FROM t2", the error message says: "Msg
208, Level 16, State 1, Line 1, Invalid object name 't2'. ".
Case 3: Strangely enough, everything is OK when run "EXECUTE AS
USER='kimliuTest'; SELECT * FROM t2".
Case 4: Everything is also OK when only run "EXECUTE AS LOGIN='kimliu'
".
Case 5: But, cannot use "EXECUTE AS LOGIN='kimliu'; SELECT * FROM t2",
the error message is the same: "Msg 208, Level 16, State 1, Line 1,
Invalid object name 't2'. ".
I am LOST! Anybody could help PLEASE~~ Thank you very much~
yours
Athos|||Hi, Athos
> Repeat, I HAVE gone through Step 5, run the script "ALTER USER
> kimliuTest WITH DEFAULT_SCHEMA = schTest " to set the default schema.
Sorry, I overlook that part when I first read your previous message.
I was able to reproduce the behaviour you mentioned, but not
consistently.
Please try the following:
USE test
EXECUTE AS LOGIN='kkhad\kimliu'
SELECT SYSTEM_USER AS SystemUser, DB_NAME() as DatabaseName,
USER as UserName, default_schema_name
FROM sys.database_principals WHERE name=USER
REVERT
What result do you get ?
Razvan
techieman:
