Re: Cross DB ownership chaining problem
From: Ale (agaio_at_econ.uba.ar)
Date: 07/11/03
- Next message: Ray Higdon: "Report SQL DB user create at Windows Event Log"
- Previous message: Vish: "row level security"
- In reply to: Dan Guzman: "Re: Cross DB ownership chaining problem"
- Next in thread: Dan Guzman: "Re: Cross DB ownership chaining problem"
- Reply: Dan Guzman: "Re: Cross DB ownership chaining problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Fri, 11 Jul 2003 08:04:13 -0300
Thanks Dan for your reply.
Unfortunately, all the databases are owned by dbo (sa), that's what leave me
disoriented.
About turning the option at the server level, I did that as a last resort
since changing each of the databases hadn't work at all.
Do you have any other suggestion?
Thanks again!
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:uy2Xo1zRDHA.2676@TK2MSFTNGP10.phx.gbl...
> Assuming your objects are owned by dbo, a likely cause of this problem
> is that the owner of database D is different than the other databases.
> The different database owner result in a broken ownership chain with
> dbo-owned objects. You can change the owner of database D using
> sp_changedbowner:
>
> USE DatabaseD
> EXEC sp_changedbowner 'sa' --specify owner of other databases
> GO
>
> BTW, you don't need to turn on cross-database chaining at the server
> level (sp_configure 'Cross DB Ownership Chaining') unless you want the
> option enabled for all databases, Turning it on at the database level
> (sp_dboption 'db chaining') for databases A, B, C and D is sufficient.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> -----------------------
> SQL FAQ links (courtesy Neil Pike):
>
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> -----------------------
>
> "Ale" <agaio@econ.uba.ar> wrote in message
> news:OFTRWxuRDHA.2852@tk2msftngp13.phx.gbl...
> > Hi all,
> >
> > We're having an issue since we installed SP3 to one of our SQLServers.
> > The server had 3 databases, A, B and C which were imported with DTS
> from
> > another SQLServer (before the SP3 was installed)
> > Database A has some stored procedures that reference tables in B, C,
> and
> > also D (which wasn't imported by that time).
> >
> > After the application of the service pack, I turned on the option of
> cross
> > DB ownership chaining for the server, and for each of the databases in
> it
> > (A, B and C)
> > All of the user stored procedures of database A work fine (including
> those
> > that reference tables in B and C), but yesterday I imported the
> missing
> > database (D) from another SQLServer, also with DTS.
> > After the import, I enabled the cross DB ownership chaining option for
> that
> > DB.
> > The stored procedures in database A that reference this new imported
> DB (D)
> > don't run. SQL Server always throws an 'Select permission denied on
> table
> > XXX'.
> > The problem happens with users other than the SA. With SA all the SPs
> work.
> > I double-checked the Cross ownership settings in the server and each
> DB and
> > everything seems to be all right.
> > I tried also restaring the service, but it didn't work.
> > Does anyone have any clue about this problem?
> > Thanks in advance for your support
> > Alex
> >
> >
>
>
- Next message: Ray Higdon: "Report SQL DB user create at Windows Event Log"
- Previous message: Vish: "row level security"
- In reply to: Dan Guzman: "Re: Cross DB ownership chaining problem"
- Next in thread: Dan Guzman: "Re: Cross DB ownership chaining problem"
- Reply: Dan Guzman: "Re: Cross DB ownership chaining problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
