Can a trigger be forced to run in a separate context than the user in SQL Server 2000?

Question

Let's say I have a SQL Server 2000 table, any name will do, it's irrelevant for this topic. On this table I have a trigger that runs after update or insert.

The user is able to insert and update the table on which the trigger is attached, but not on other tables that the trigger targets.

If the user modifies data in the original table, I get an exception complaining that the user doesn't have permission to modify data in the target tables of the trigger.

I assume this is caused by the fact that the trigger is running in the context of the user. Is there a way to have the trigger run in its own context or am I not interpreting the cause of this exception correctly?

Edit: I should point out that I'm using SQL Server 2000, so using EXECUTE AS won't work.

Answer 1

Triggers do normally operate with the permissions of the user who made the initial change. A workaround for something like this is for the trigger to write the data into a temporary table and then have a separate process (logged in as a higher-level user) check for data in the temporary table every so often and move it into the target table.

Answer 2

http://msdn.microsoft.com/en-us/library/ms189799.aspx

EXECUTE AS Specifies the security context under which the trigger is executed. Enables you to control which user account the instance of SQL Server uses to validate permissions on any database objects that are referenced by the trigger.

Answer 3

Which version of SQL Server are you using? I was just able to do it without any problems in SQL Server 2005:

CREATE TABLE dbo.Test_Trigger_1
(
    my_string VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Test_Trigger_1 PRIMARY KEY CLUSTERED (my_string)
)
GO

CREATE TABLE dbo.Test_Trigger_2
(
    my_string VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Test_Trigger_2 PRIMARY KEY CLUSTERED (my_string)
)
GO
CREATE TRIGGER dbo.tri_Test_Trigger_1
ON dbo.Test_Trigger_1
FOR INSERT
AS
BEGIN
    INSERT INTO dbo.Test_Trigger_2
    (
     my_string
    )
    SELECT
     my_string
    FROM
     INSERTED
END
GO

Then I created a login that only had access to Test_Trigger_1, I confirmed that it couldn't access Test_Trigger_2, I inserted a row into Test_Trigger_1 and a row appeared in Test_Trigger_2.