Re: Permission Denied executing SP that reads foreign table!
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/31/05
- Previous message: m.bohse_at_quest-consultants.com: "Re: SQL Server 2005 encryption with Smart Card certificates"
- In reply to: Steve Le Monnier: "Permission Denied executing SP that reads foreign table!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 31 Oct 2005 07:11:43 -0600
> When a stored procedure is executed the user calling the SP may not have
> access to the base tables, but that doesn't matter as the SP runs under
> the creators rights and in this case its the dbo who owns the SP. So far
> so good.
This statement is not entirely correct; the proc doesn't run under the
creator's (owner) rights. Permissions on objects referenced by the proc are
not checked as long as the objects involved have the same owner. This is
called an unbroken ownership chain.
> Why is this? All objects across these databases on the same server are
> owned by the same person... the DBO. so why doesn't this work.
Cross-database ownership chaining is a little confusing because of
login/user mapping. It is not the name of the object owner that controls
the ownership chain, it is the login associated with the object owner.
Since the login mapping for the 'dbo' user is determined by database
ownership, both databases need to have the same owner in order to provide an
unbroken ownership chain for dbo-owned objects.
If you are using SQL 2000 SP3 or above, you need to also enable the 'db
chaining' database option for the databases involved. This should be done
only when you fully trust those users that have permissions to create
dbo-owned objects. See the Books Online for more information.
-- Hope this helps. Dan Guzman SQL Server MVP "Steve Le Monnier" <steve_lemon@hotmail.com.nospam.com> wrote in message news:OBrDEOh3FHA.2432@TK2MSFTNGP10.phx.gbl... > I thought I had a reasonable understanding of how Stored Procedure > security worked, but I've been caught out by something and now I'm a > little puzzled. > > When a stored procedure is executed the user calling the SP may not have > access to the base tables, but that doesn't matter as the SP runs under > the creators rights and in this case its the dbo who owns the SP. So far > so good. > > However we have split our tables across two database (still on the same > server however). We have company data DB's where all requests originate > and to save time and duplication we have a central database holding > information that is common to all company DB's. > > When an SP is executed on a company DB that needs to select data from the > central DB, we get a permission denied message. > > Why is this? All objects across these databases on the same server are > owned by the same person... the DBO. so why doesn't this work. > > In order to resolve this it seems I'm going to have to give select > permissions on certain tables within this central DB and that is what I > was trying to avoid. > > Any help on the mechanics of this would be gratefully received > > Steve Le Monnier > > >
- Previous message: m.bohse_at_quest-consultants.com: "Re: SQL Server 2005 encryption with Smart Card certificates"
- In reply to: Steve Le Monnier: "Permission Denied executing SP that reads foreign table!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
