Re: "SELECT permission denied on object", on view accessing tables in another database
From: Russell Fields (RussellFields_at_NoMailPlease.Com)
Date: 09/07/04
- Previous message: Dave: ""SELECT permission denied on object", on view accessing tables in another database"
- In reply to: Dave: ""SELECT permission denied on object", on view accessing tables in another database"
- Next in thread: Dave: "Re: "SELECT permission denied on object", on view accessing tables in another database"
- Reply: Dave: "Re: "SELECT permission denied on object", on view accessing tables in another database"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 7 Sep 2004 16:01:41 -0400
Dave,
By default, after SQL Server 2000 SP3, ownership chaining between database
was broken unless you set it specially for your server. The reason,
cross-database ownership chaining opened some security breach opportunities.
Therefore, SS2K no longer thinks that VIEWER.dbo is the same user as
VIEWEE.dbo. So, if you want to avoid anyone seeing the tables, you will
need to have views in the same database with the tables.
Russell Fields
"Dave" <dpt@nospam.eliassen.com> wrote in message
news:ebLko8QlEHA.1376@TK2MSFTNGP12.phx.gbl...
> (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
>
>
- Previous message: Dave: ""SELECT permission denied on object", on view accessing tables in another database"
- In reply to: Dave: ""SELECT permission denied on object", on view accessing tables in another database"
- Next in thread: Dave: "Re: "SELECT permission denied on object", on view accessing tables in another database"
- Reply: Dave: "Re: "SELECT permission denied on object", on view accessing tables in another database"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
