June 5, 2013 at 5:03 am
Im a little confused. I am moving a database from 2005 to 2008. The user the application connects as is called webuser and is the owner of the database as well as the schema also called webuser.
All tables are named "webuser.table1", "webuser.table2" etc.
If I perform the following
"Select * from table1"
I receive and Msg 208, Level 16, State 1, Line 1
Invalid object name 'fs_categories'. error.
If I perform the following:
"Select * from webuser.table1"
Then I get the correct result.
I'm confused why this is as the user is set up in the database with webuser as it's default schema. This problem doesn't happen in the original 2005 instance only the 2008 instance.
Thanks.
June 5, 2013 at 5:07 am
Try running SELECT USER_NAME(), SCHEMA_NAME() to see what user and schema your session is connected as.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 5, 2013 at 5:53 am
Thanks for the reply.
Ah, interesting. So I am connecting as DBO for both user and schema, yet on the original instance I connect as webuser for both user and schema.
Hmmm.. So even though the webuser sql login is mapped to the webuser database user and default schema is set as webuser, it's still connecting as dbo.
I'll keep hunting myselft, but any ideas what I am missing in the user setup?
June 5, 2013 at 5:59 am
Aha! I am an idiot. I had set the user up as a sysadmin as the app needed that permission when moving over to the new server. This will default to dbo schema. I have removed it's sa permission and it's now working!
Thanks for the help.
June 5, 2013 at 6:00 am
Have you granted any server level permissions like sysadmin to the web user?
edit: 4 seconds too late!
June 5, 2013 at 6:04 am
I believe that the default schema for the user is checked for the existence of an unqualified table name, then the dbo schema is checked. I don't know why the behavior would be any different in 2008. Either way, if you have tables of the same name in more than one schema in the same database, I think I would qualify all references to them. If nothing else, the clarity will help others who maintain the code.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply