Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
martalex
Starting Member
17 Posts |
 Posted - 2007-08-29 : 10:49:24
|
| I have a table owned by a specific user that is reference from a SP owned by DBO.When I run my query I'm getting "Invalid object name" on that table. It works if I prefix the table with the owner in the SP but I can't because that table is own by different user in different version of the database. Any idea how I could avoid hard coding the owner's name? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 11:11:37
|
| I reckon either change the Owner of that Object, or hard code it, or use dynamic SQL.I also reckon that that would be my order of preference!Kristen |
 |
|
|
martalex
Starting Member
17 Posts |
Posted - 2007-08-29 : 21:09:48
|
| What is the logic behind the SP not recognizing the table with different owner that dbo? BTW the SP was called using the table owner user |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-30 : 01:44:21
|
| If you don't specify object owner, sql will use dbo as default. In your case, sp is owned by dbo while the table called is owned by other user so there is no ownership chain. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-30 : 04:33:44
|
| "If you don't specify object owner, sql will use dbo as default"I thought if no explicit owner then SQL would look for an object owned by the current logged on person first, and if none found then DBO.Note that if the Sproc is named starting with "sp_" then SQL will look first in MASTER database (Username then DBO) and if nothing found then look in current database.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-30 : 22:13:46
|
| It's true if call object directly. But in this case, table is queried in the sp. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-31 : 03:35:54
|
| Of course! Never really thought of it like that as we always prefix tables, in all our source code, with owner name (well, always "dbo" in our case!)Kristen |
|
|
|
|
|
|
Invalid object name when object own by user in SP