Re: DELETE permission denied problem when using a stored proc to d

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/13/05 

Date: Wed, 12 Oct 2005 18:56:00 -0500

Did you peruse the article in the link Jens posted? In addition to enabling
the 'db chaining' database option (or cross-database chaining server option)
the databases need to have the same owner so that the dbo-owned object
ownership chain is unbroken. You can use sp_changedbowner to make the
database owners the same. For example:

USE MyDB1
EXEC sp_changedbowner 'SomeLogin'
USE MyDB2
EXEC sp_changedbowner 'SomeLogin'
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Bucknell" <Buckers@newsgroups.nospam> wrote in message 
news:712793A4-B649-4FA9-B309-BA5DB1EC4EB4@microsoft.com...
> Thanks for the points, I didn't have chaining enabled.  I have enabled
> chaining but I'm still getting the same error of DELETE permission denied
> when I try and delete the data from the second database.
>
> "Jens" wrote:
>
>> As long as you dont activate ownership chain (as I assume that you are
>> deleting data in a different database) this won´t work. Ownerchip
>> chains is disabled by default since SP3.
>>
>> Look for cross database ownership chain in BOL or for the thread:
>>
>> http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/4b86a2ccefd974af
>>
>> HTH, JEns Suessmeyer.
>>
>>

Relevant Pages

  • Re: Permissions on sp_OASetProperty
    ... this be setup with the least amount of permissions being given. ... chaining to allow limited access to the sp_OA* procs and other potentially ... dbo-owned objects in that database. ... EXEC sp_dboption 'MyDatabase', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • Re: Using Views for Security
    ... A security consideration when cross-database chaining enabled is that you ... must fully trust users that have permissions to create dbo-owned objects. ... in order to access objects in a database. ... It is on cross-database ownership. ...
    (microsoft.public.sqlserver.security)
  • Re: Security - Stored Procedures
    ... This is known as ownership chaining. ... Permissions on indirectly referenced objects are not required. ... owned by the same login since database ownership determines the user/login ...
    (microsoft.public.sqlserver.security)
  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... Not only can different database principal names have the same SID, it's not unusual to have the same principal name with different SIDs. ... This is confusing at first glance because all objects/schema are owned by 'dbo' but it is really the login SID that is used for chaining. ... I see that the owner is not the schema itself, so it must be a USER entity, or a LOGIN entity, or some dynamic combination of them. ... If I get this sorted out into a set of steps minus fluff, any suggestions on where I might post a "How To" on Ownership Chaining? ...
    (microsoft.public.sqlserver.security)
  • Re: "SELECT permission denied on object", on view accessing tables in another database
    ... chaining with the 'db chaining' database option. ... > if the VIEWER database is on a seperate server, ...
    (microsoft.public.sqlserver.security)
Loading