Hi
Having some issues with our apps.
We are trying to get our applications to work with sql2005.
Ive got the databases "setup", and all our apps run fine...
...except for when queries are made without the owner of the
table being specified in the query.
The connection is opened with the username that is associated with that owner.
And it fails in Manager as well. Is there something im missing, because you should
be able to do this.
eg:
select * from <table_name>
Gives the error:
Msg 208, Level 16, State 1, Line 1
Invalid object name '<table_name>'.
However if i were to query like this:
select * from <owner>.<table_name>
it works fine.
? Hi s1981s, What you're seeing is the result of a change from SQL Server 2000 to SQL Server 2005. In SQL Server 2000, table names were built like this: <server>.<database>.<owner>.<tablename> In SQL Server 2005, this has changed to this: <server>.<database>.<schema>.<tablename> Instead of owner, the schema is now used as a qualifier. If you omitted a qualifier in SQL Server 2000, the DB would first try to find a matching table with the current user as owner. If there is none, then it would try dbo.<tablename>. And if that fails as well, you'd get an error message. In SQL Server 2005, the steps taken to find a table if no schema is specified are change to: 1. look for the table in the schema that is designated as the user's default schema (this can be changed with the ALTER USER command); 2. if not found, look in the dbo schema. The possible solutions for your problem are: 1. Add schema name to all table references in your code (recommended - prevents unexpected side effects and is the fastest), or 2. Set each user's default schema to the schema that the tables are created in, or 3. Move all tables to the dbo schema. -- Hugo Kornelis, SQL Server MVP <s1981s@.discussions.microsoft.com> schreef in bericht news:c304ed17-b417-45fc-9342-89392732991f@.discussions.microsoft.com... Hi Having some issues with our apps.We are trying to get our applications to work with sql2005.Ive got the databases "setup", and all our apps run fine......except for when queries are made without the owner of thetable being specified in the query.The connection is opened with the username that is associated with that owner.And it fails in Manager as well. Is there something im missing, because you shouldbe able to do this. eg:select * from <table_name> Gives the error: Msg 208, Level 16, State 1, Line 1 Invalid object name '<table_name>'. However if i were to query like this:select * from <owner>.<table_name> it works fine.|||Hi Hugo,
Thanks for your response.
Your solutions were:
The possible solutions for your problem are:
1. Add schema name to all table references in your code (recommended - prevents unexpected side effects and is the fastest), or
2. Set each user's default schema to the schema that the tables are created in, or
3. Move all tables to the dbo schema.
In Relation to item 2: in your post
Ive created a schema of the same name as the user and owner of the table.
Given tables that as there default schema. Would there be anything else i would need to
do? Also i keep changing the default schema, but it changes back to dbo all the time, is this because of the database owner being sa?
If the database owner is a completely different user (sa/dbo) would this cause an issue with schemas for another user?
Unfortunately item 1 is not the fastest out of the 3 options due to the size of the program, and the time in which we have to install on site.
Could you possibly elaborate further on item 2, or point me towards some reading that could be helpful?
Cheers,
Simon
EDIT:
Hugo,
Having changed and added the schema to the table objects as the default i now get the following error:
"The multi-part identifier "transactions.cost" could not be bound.
What would this be related to?
|||? Hi Simon, >> In Relation to item 2: in your post>> Ive created a schema of the same name as the user and owner of the table.>> Given tables that as there default schema. Would there be anything else i would need to>> do? First question is: how did you create the user? There are, as far as I know, three ways to create a user in SQL Server 2005. 1. Using CREATE USER NewUser [...] WITH DEFAULT_SCHEMA MySchema If NewUser refers to an unqualified table (SELECT .... FROM SomeTable), SQL Server will first try MySchema.SomeTable, then dbo.SomeTable, then throw an error. 2. Using CREATE USER NewUser [...] - but without a WITH DEFAULT_SCHEMA clause. The default schema is defaulted to dbo. For SELECT .... FROM SomeTable, SQL Server will only try dbo.SomeTable. 3. Same way as you always did on SQL Server 2000: with sp_adduser 'NewUser'. Stop using this stored procedure. It is included for backward compatibility reasons, but BOL warns you to replace it with CREATE USER because sp_adduser will be removed in a future version. Annyway, for backwards compatibility reasons, this user will have the name of the default schema equal to the username. So for SELECT ... FROM SomeTable, SQL Server will first attempt NewUser.SomeTable, then dbo.SomeTable, then throw an error. Note that in all these cases, you can always change the default schema with ALTER USER NewUser WITH DEFAULT_SCHEMA = NewSchema. And if you don't know someone's default schema, you can find out by having him or her execute SELECT SCHEMA_NAME(). Or you can find out for alll users by executing SELECT name, default_schema_name FROM sys.database_principals. One step is knowing (either by querying or by setting) the default schema for your users. The other step is knowing (again, by querying or checking) what schema your objects are created in. You can check this with SELECT name, type_desc, SCHEMA_NAME(schema_id) FROM sys.objects. And you can move a table to a different schema with ALTER SCHEMA NewSchema TRANSFER OldSchema.SomeTable. >> Also i keep changing the default schema, but it changes back to dbo all the time, is this because of the database owner being sa? That should not happen. Can you describe exactly what steps you take to change the default schema? >> If the database owner is a completely different user (sa/dbo) would this cause an issue with schemas for another user? Because schema and user are now seperated, there shouldn't be any relation. The dbo schema is not directly related to the database owner. >> Having changed and added the schema to the table objects as the default i now get the following error:>>>> "The multi-part identifier "transactions.cost" could not be bound.>> What would this be related to? Hard to tell, without seeing any code. But I would look for code that refers to a table named "transactions.cost". Then check if there is a table named "cost" in a schema named "transactions". If you find the code where it happens but can't spot an error, then copy the code in a reply to this message. -- Hugo Kornelis, SQL Server MVP|||
I am having the same problem as Simon. I just upgraded my development system to SQL 2005 Express SP1. My customer is still on SQL 2000. The question is, I am going to need to downgrade back to SQL 2000, or can I use SQL 2005 while the production runs on SQL 2000?
SELECT name, type_desc, SCHEMA_NAME(schema_id) as [schema] FROM sys.objects
shows:
name type_desc schema
================================
Questions USER_TABLE dbo
QuestionTypes USER_TABLE topcall
After executing the command:
ALTER USER topcall WITH DEFAULT_SCHEMA = topcall
SELECT name, default_schema_name FROM sys.database_principals
Shows:
name default_schema_name
=========================
topcall topcall
Now when I enter in SQL Server Management Studio:
select top 5 * from questions
select top 5 * from questiontypes
The first query succeeds, but the second one fails with:
Msg 208, Level 16, State 1, Line 12
Invalid object name 'questiontypes'.
So even after making the changes suggested, I still get the same error. Anything else I should be doing? If I understand schema's well, then it is just a name. So I don't have to create a "shema" called topcall here, right? By the way it is rather irritating that when I look at the database properties it shows it is running in SQL 2000 compatibility mode, but a very basic behavior like this, obviously doesn't work out of the box.
|||Oh, I forgot. I use SQL security and I did logon as user topcall|||
I logged into SQL Server Management Studio with user topcall and ran the following queries:
select schema_name()
dbo
select user, user_name()
dbo dbo
I wrote a test program using ADO using ODBC as connection and also here I logged on as user topcall. And got the same user name and therefore default schema returned. What is going on here?
When I try to change the default schema for user dbo:
ALTER USER dbo WITH DEFAULT_SCHEMA = topcall
I get as error:
Msg 15150, Level 16, State 1, Line 1
Cannot alter the user 'dbo'.
|||
In your case, it appears that topcall is either a sysadmin or is the owner of the database, so he is mapped to user dbo, whose default schema is dbo and cannot be changed.
Thanks
Laurentiu
That's IT! The user topcall was both owner and sysadmin. I changed it to be a normal user and now it does work correctly.
Thank you very much Laurentiu.
|||We are running into the same problem with SQL Server 2005 Express and have tried every suggestion listed here without change. We have set up the exact same thing in SQL Server 2005 Enterprise without issue. Our goal is to determine if SQL Server 2005 Express is a viable option for our application. Here is our situation:
Login: IMS (only in the dbcreator role which we have tried removing without any change)
User: IMS - login = IMS, default schema = IMS (user IMS is not a member of any database roles but we have tried making him a member of all but the two "deny" roles)
Schema: IMS - owned by user IMS
Query is accessing a table called IMS.TransactionTypes_IMS6 without specifying the schema in the SQL statement (i.e. "SELECT * FROM TransactionTypes_IMS6"). In SQL Server 2005 Enterprise it assumes that we want the table under the IMS schema since this is the default schema for the user we are connecting to the database with (IMS). The same set up in SQL Server 2005 Express does not make this assumption, however, and throws the error, "Invalid object name 'TransactionTypes_IMS6'." Changing the code to specify the schema is not an option as this is legacy code migrated up to .Net and there are too many places that would need to change.
It sounds like you were able to resolve this issue with SQL Server 2005 Express and any help would be greatly appreciated. We have tried everything!
No comments:
Post a Comment