DELETE permission denied problem when using a stored proc to delet

I'm using a stored proc to delete a record in two tables (in two different
databases) and I keep receiving “Error Number: 229 -- Error State: 5 -- Er
ror
Message: DELETE permission denied on object 'ewBehaviour', database
'eWorkSpaceV5', owner 'dbo' ”. The stored proc works for me (as sysadmin
for
the server), but won’t work for any other user. I’ve tried giving a use
r
db_owner access for both the databases but I still receive the error.
Below is the stored proc:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.spWEB_Delete_Detention
@.DetentionID as int
AS
SET XACT_ABORT ON
DECLARE @.BehaviourId as int
IF NOT EXISTS
(
SELECT DetentionID
FROM DC_Detentions
WHERE DetentionID=@.DetentionID
)
BEGIN
RAISERROR ('Detention does not exist in DC_Detention ',16,1)
RETURN -1
END
IF NOT EXISTS
(
SELECT Id
FROM eWorkSpaceV5.dbo.ewBehaviour
WHERE ID =
(SELECT Link
FROM DC_Detentions
WHERE DetentionID=@.DetentionID)
)
BEGIN
RAISERROR ('Behaviour entry does not exist in ewBehaviour',16,1)
RETURN -1
END
SELECT @.BehaviourId=Link
FROM DC_Detentions
WHERE DetentionID=@.DetentionID
BEGIN TRANSACTION
print 'Begin Transaction'
print 'Try Delete DC_Detentions'
DELETE FROM DC_Detentions
WHERE (DetentionID = @.DetentionID)
IF @.@.ERROR<>0 or @.@.ROWCOUNT<>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not delete Detention from DC_Detention',16,1)
print 'Delete from DC_Detention failed'
RETURN -1
END
print 'Try Delete eWorkSpaceV5 ewBehaviour'
DELETE FROM eWorkSpaceV5.dbo.ewBehaviour
WHERE (Id = @.BehaviourId)
IF @.@.ERROR<>0 or @.@.ROWCOUNT<>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not delete Detention into ewBehaviour',16,1)
print 'Delete from ewBehaviour failed'
RETURN -1
END
COMMIT TRANSACTION
RETURN 0
SET XACT_ABORT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOAs long as you dont activate ownership chain (as I assume that you are
deleting data in a different database) this won=B4t 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/micro...ramming/browse=
_frm/thread/4b86a2ccefd974af
HTH, JEns Suessmeyer.