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 -- Error
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 user
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
GO