Re: Permission Denied executing SP that reads foreign table!

From: Steve Le Monnier (steve_lemon_at_hotmail.com.nospam.com)
Date: 11/01/05 

Date: Tue, 1 Nov 2005 15:49:02 -0000

Thanks for the input Dan, I'm going to hit the Books On Line to read up on
DB Chaining.

Cheers

Steve Le Monnier

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:eJrlkyh3FHA.3976@TK2MSFTNGP15.phx.gbl...
>> 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
>>
>>
>>
>
>

Relevant Pages

  • Re: Question about dropping owners permissions...
    ... When a member of the db_owner role creates an object, ... dbo, must the owner must be specified: ... >>> create databases themselves. ... Can anyone help me reach my end goal: Allowing developers to ...
    (microsoft.public.sqlserver.security)
  • Re: Controlling create & drop proc, view privilege
    ... A user needs to be a member of db_owner or db_ddladmin ... roles to create a objects owned by dbo. ... It depends first on ownership the ownership chain. ... branch where the owner of the object is different. ...
    (microsoft.public.sqlserver.security)
  • Re: Controlling create & drop proc, view privilege
    ... > roles to create a objects owned by dbo. ... It depends first on ownership the ownership chain. ... > permissions to execute the stored procedure only. ... > branch where the owner of the object is different. ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions and ODBC
    ... objects have the same owner. ... case of dbo-owned objects in different databases, ... See the SQL 2003 SP3 Books Online for more ... > The Ms Access program executes a stored procedure that resides in SQL ...
    (microsoft.public.sqlserver.server)
  • Re: Two DB Owners
    ... full permissions in all databases. ... Note that the user will still be known as the 'dbo' user as long as the ... I need to add the login 'BUILTIN\Administrators' ... > 'DYNGRP' and 'BFGROUP' roles of the Dynamics and DAVCatalog databases. ...
    (microsoft.public.sqlserver.security)