Is it possible to run a trigger in a separate context than a SQL Server 2000 user?
Let's say I have a SQL Server 2000 table, any name will do, this is not relevant to this topic. This table has a trigger that fires after an update or insert.
The user can insert and update the table on which the trigger is connected, but not on other tables that the trigger is targeting.
If the user modifies data in the source table, I get an exception complaining that the user does not have permission to modify data in the trigger target tables.
I assume this is because the trigger is running in the context of the user. Is there a way to run the trigger in its own context or am I misinterpreting the reason for this exception?
Edit: I have to point out that I am using SQL Server 2000, so using EXECUTE AS will not work.
a source to share
Triggers usually work with the permissions of the user who made the original change. A workaround for something like this is that the trigger writes the data to the temp table and then has a separate process (logs in as a higher user) to check the data in the temp table as often and move it to the target table.
a source to share
What version of SQL Server are you using? I was just able to do this without issue 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 could not access Test_Trigger_2, I inserted a row in Test_Trigger_1 and the row appeared in Test_Trigger_2.
a source to share