"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



Relevant Pages

  • Re: "SELECT permission denied on object", on view accessing tables in another database
    ... By default, after SQL Server 2000 SP3, ownership chaining between database ... cross-database ownership chaining opened some security breach opportunities. ... I have two databases, VIEWER and VIEWEE. ...
    (microsoft.public.sqlserver.security)
  • Re: akireru
    ... > viewer, but the viewee. ... This is another case of mismatch between English and Japanese. ...
    (sci.lang.japan)
  • Re: akireru
    ... Danny Wildeek idatzi du 3/21/2005 1:40 PM: ... viewer, but the viewee. ... visibility. ...
    (sci.lang.japan)