Re: "SELECT permission denied on object", on view accessing tables in another database
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/08/04
- Previous message: anonymous_at_discussions.microsoft.com: "securing table with credit card #'s"
- In reply to: Dave: "Re: "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 19:18:38 -0500
To add to Russell's response, you can selectively enable cross-database
chaining with the 'db chaining' database option. Also, both databases need
to be owned the same login in order to maintain an unbroken chain for
dbo-owned objects. Below is a sample implementation script.
Note that you should fully trust db_owner and db_ddladmin role members
before enabling cross-database chaining.
EXEC sp_dboption 'VIEWER', 'db chaining', true
EXEC sp_dboption 'VIEWED', 'db chaining', true
GO
USE VIEWER
EXEC sp_changedbowner 'SomeLogin'
GO
USE VIEWED
EXEC sp_changedbowner 'SomeLogin'
GO
-- Hope this helps. Dan Guzman SQL Server MVP "Dave" <dpt@nospam.eliassen.com> wrote in message news:OJIxamRlEHA.3356@TK2MSFTNGP15.phx.gbl... > Thanks!! So, that answers my question, at least. > > Here's something I'm still confused about, though. I can get this to work > if the VIEWER database is on a seperate server, using linked servers. Why > would that be? This is how my code has been working for quite some time. > (By the way - the code in question is VB code used on a web site.) The > reason I want to move the VIEWER database to the same server as the VIEWED > database is that views between servers started running VERY slowly, when > we > upgrades from SQL 7 to SQL 2000. The same queries/views are much faster > when on the same server. But, we have security issues. I'd really much > prefer to have the login user that the web site uses have no explicit > rights > to any tables in VIEWED. It's better for security and for > "isolation/portability" of the code. > > - Dave > > "Russell Fields" <RussellFields@NoMailPlease.Com> wrote in message > news:u2LHDWRlEHA.3896@TK2MSFTNGP15.phx.gbl... >> 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: anonymous_at_discussions.microsoft.com: "securing table with credit card #'s"
- In reply to: Dave: "Re: "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
|
|
