Execute As , trigger problem

Question

Post reply

Execute As , trigger problem

kgunnarsson

Ten Centuries

Points: 1218
More actions
January 9, 2008 at 3:15 am

#73951

Hi all.
I'm trying to figure out how to solve the problem below. We have a database that is used by 200 people, now a trigger in this database needs to update a row in a different database.(row is found by using an external_document_no field)
This needs to be accomplished without allowing all user access to the table in the other database and needs to be done by using an trigger after delete.
CREATE TRIGGER [dbo].[trg_updateWorklog]
ON [dbo].[Updates]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
declare @update_filter int
declare cur cursor for select cast([External Document No_] as int) from deleted
open cur
fetch next from cur into @update_filter
while @@fetch_status = 0
begin
if (@update_filter> 0)
begin
UPDATE dest_db.dest_table_schema.dest_table SET update_field = getdate() WHERE update_field = @update_filter
end
fetch next from cur into @worklogid
end
close cur
deallocate cur
END
The above code will give me an error saying that user has no rights to read or write in the dest_table. Now in sql 2005 I would simply use execute as login = ‘my login’ is there anything like that in sql 2000 ?
Any ideas.
kgunnarsson
Mcitp Database Developer.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kgunnarsson Ten Centuries Points: 1218 More actions · Answer 1

The was solved by the following but ugly solution.

1.) Create a table in the local database.

2.) Make the trigger dump the external number in that database (All users have read/write in the dbo schema)

3.) Create and stored procedure that queries the local table and update the table in the external database.

4.) use sql agent to run the stored procedure as an user with rights in the destination database.

ugly but works

kgunnarsson
Mcitp Database Developer.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

That's really the only way to make this work. The trigger will always execute as the person updating the table, and it would need rights to the other database if it directly moved the data.

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 3

Ananth-431739

Ten Centuries

Points: 1185

January 11, 2008 at 4:53 am

#767033

I recommend you to check the user rights....

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

If you have an answer to this, Ananth, just tell us... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)