MSSQL - Cannot perform SET operation

[sistasi]

Hi,

I am using MS SQL 2000 version 8.0.
I am trying to run this stored proc to archive my tables.
what i did is just to delete and put a record on my archive_log table.

CREATE            PROCEDURE dbo.archival
(
	@retCode as integer	output
) AS
SET NOCOUNT ON

declare	@archival_run_date datetime,
	@start_date datetime,
	@rows integer,
	@error integer

set @retCode = 1
set @archival_run_date = cast (floor (cast (getdate() as float)) as datetime)

set @start_date=getdate()

delete from Deal where LastChgDate < dateadd(Month, -18, @archival_run_date)

set @rows = @@rowcount
set @error = @@error

insert into Archive_Log (Table_Name, Status, No_of_records, Run_Date, Start_time, End_Time)
	select 'Deal', case when @error = 0 then 'Success' else 'Fail' end Status, @rows, @archival_run_date, @start_date, getdate() 

if @error <> 0
begin
	set @retCode = 0
end

--------------------------------------------------

set @start_date=getdate()

delete from Event where LastChgDate < dateadd(Month, -18, @archival_run_date) 
And ID not in 
( 
	select distinct EventID from Deal 
)

set @rows = @@rowcount
set @error = @@error

insert into Archive_Log (Table_Name, Status, No_of_records, Run_Date, Start_time, End_Time)
	select 'Event', case when @error = 0 then 'Success' else 'Fail' end Status, @rows, @archival_run_date, @start_date, getdate() 

if @error <> 0
begin
	set @retCode = 0
end

--------------------------------------------------

set @start_date=getdate()

delete from DealHistory where LastChgDate < dateadd(Month, -18, @archival_run_date)
And ID not in
(
	select distinct ID from Deal
)

set @rows = @@rowcount
set @error = @@error

insert into Archive_Log (Table_Name, Status, No_of_records, Run_Date, Start_time, End_Time)
	select 'DealHistory', case when @error = 0 then 'Success' else 'Fail' end Status, @rows, @archival_run_date, @start_date, getdate() 

if @error <> 0
begin
	set @retCode = 0
end

--------------------------------------------------

set @start_date=getdate()

delete from EventHistory where LastChgDate < dateadd(Month, -18, @archival_run_date)
And ID not in
(
	select distinct ID from Event
)

set @rows = @@rowcount
set @error = @@error

insert into Archive_Log (Table_Name, Status, No_of_records, Run_Date, Start_time, End_Time)
	select 'EventHistory', case when @error = 0 then 'Success' else 'Fail' end Status, @rows, @archival_run_date, @start_date, getdate() 

if @error <> 0
begin
	set @retCode = 0
end

SET NOCOUNT OFF
GO

However the first 2 tables that i have (Event and Deal) has trigger, everytime I inserted/updated/deleted the record in the tables, it will use this trigger, same for Event.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER    TRIGGER trg_del_Deal ON dbo.Deal
FOR DELETE
AS
BEGIN

SET IDENTITY_INSERT DealHistory ON

 INSERT INTO DealHistory (ID, EventID, DealName, CreatedDate, LastChangedDate)
SELECT 
		del.ID,
		del.EventID,
		del.DealName,
		del.CreatedDate,
		getdate()
from deleted del

SET IDENTITY_INSERT DealHistory OFF
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

And i'm getting this error:

The current user is not the database or object owner of table 'DealHistory'. Cannot perform SET operation. 

Any idea how to solve this???
I have added the SET IDENTITY_INSERT DealHistory OFF too but it didn't work, still getting the same error.

The role used by the user to execute this SP is granted to these tables and SP.
Can anyone help??

Thanks.

[skhanal]

Are you logged in as a dbo user?. Check if INSERT permission is granted on DealHistory to the login you are using.

[rmiao]

Only table owner or dbo can set identiy insert.

[sistasi]

skhanal: yes the insert, update, delete for all these tables are all granted to the user who execute this sp.

rmiao: well.. only the owner?
however i have 2 environment, both has the same configuration, same privilege. one is able to perform those sp, another one got this error... any idea why?

[rmiao]

Does user have sysadmin or db_owner rights via group membership or so on working environment?

[sistasi]

the user has public role and another role, but there it is not dbo role.
both accounts in 2 env has the same configuration.

[rmiao]

Is it windows account? Who is owner of DealHistory?