"SELECT permission denied on object", on view accessing tables in another database

From: Dave (dpt_at_nospam.eliassen.com)
Date: 09/07/04


Date: Tue, 7 Sep 2004 15:16:18 -0400


(Names changed for simplicity)

Scenario: I have two databases, VIEWER and VIEWEE. VIEWEE contains tables,
and VIEWER contains views into the tables in VIEWEE. Both databases and the
tables in them are on the same server, and are owned by "dbo". I have a
user, call it 'XX', with rights to do selects on the views in VIEWER, but
with no rights in VIEWEE.

When I log in as xx, in query analyzer (or anywhere else), and try to select
from a view in VIEWER, call it "VIEWTABLE", which views into a table in
VIEWEE called "VIEWEDTABLE", I get:

"SELECT permission denied on object 'VIEWEDTABLE', database 'VIEWEE', owner
'dbo'."

Help! I've searched dejanews about this, and found several people
suggesting that the issue is caused by tables not having the same owner as
the views. In my case, though, both are owned by "dbo". Does the problem
have to do with the fact that the views and the viewed tables are in
different databases?

- Dave