Delete records permission

Hello,
I have a couple of tables in a SQL Server 2005 database back end and I want
to delete some records through a query from a MS
Access 2003 front end. I get an error in MS Access saying "Could not delete
from specified tables". There are multiple users of the
back end and each one logs in to SQL Server using Windows Authentication. Ea
ch user has the db_datareader and db_datawriter
permissions checked for the database role membership. SELECT queries work an
d I can see the contents of the tables but the DELETE
query cannot run from MS Access. I have a feeling I need to set up more perm
issions. Does anyone know how to get this working?
Cheers,
Max.Max,
Does the table you are trying to delete from have a primary key? If not, you
may need to add one.
-- Bill
"Max" <maxy_100@.yahoo.com> wrote in message
news:OyWc3GOMHHA.140@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a couple of tables in a SQL Server 2005 database back end and I
> want to delete some records through a query from a MS
> Access 2003 front end. I get an error in MS Access saying "Could not
> delete from specified tables". There are multiple users of the
> back end and each one logs in to SQL Server using Windows Authentication.
> Each user has the db_datareader and db_datawriter
> permissions checked for the database role membership. SELECT queries work
> and I can see the contents of the tables but the DELETE
> query cannot run from MS Access. I have a feeling I need to set up more
> permissions. Does anyone know how to get this working?
> Cheers,
> Max.
>

Delete records permission

Hello,
I have a couple of tables in a SQL Server 2005 database back end and I want to delete some records through a query from a MS
Access 2003 front end. I get an error in MS Access saying "Could not delete from specified tables". There are multiple users of the
back end and each one logs in to SQL Server using Windows Authentication. Each user has the db_datareader and db_datawriter
permissions checked for the database role membership. SELECT queries work and I can see the contents of the tables but the DELETE
query cannot run from MS Access. I have a feeling I need to set up more permissions. Does anyone know how to get this working?
Cheers,
Max.Max,
Does the table you are trying to delete from have a primary key? If not, you
may need to add one.
-- Bill
"Max" <maxy_100@.yahoo.com> wrote in message
news:OyWc3GOMHHA.140@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a couple of tables in a SQL Server 2005 database back end and I
> want to delete some records through a query from a MS
> Access 2003 front end. I get an error in MS Access saying "Could not
> delete from specified tables". There are multiple users of the
> back end and each one logs in to SQL Server using Windows Authentication.
> Each user has the db_datareader and db_datawriter
> permissions checked for the database role membership. SELECT queries work
> and I can see the contents of the tables but the DELETE
> query cannot run from MS Access. I have a feeling I need to set up more
> permissions. Does anyone know how to get this working?
> Cheers,
> Max.
>

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.