Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wivster
Starting Member
2 Posts |
 Posted - 2003-02-25 : 04:59:32
|
| We have a database (A) with individual SQL Server security logins. We have a trigger on a table that is fired on insert or update to update a table in another database (B) in another server. It is a linked server but the other database (B) does not use the same security model and uses Window Auth. The trigger is failing because the users are not in database (B). Can we run the trigger as dbo for example so the trigger works and bypasses the security? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-25 : 09:03:49
|
| The rtigger will run as part of the transaction that fires it and so as the same user.Instead of the trigger updating the remote database it could put a command into a table and a scheduled task could execute the command as sa.This won't fail the update if the remote operation fails but will get round the security problem.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
wivster
Starting Member
2 Posts |
Posted - 2003-02-26 : 05:14:02
|
| The problem is we need the updates to be on Server B within a few seconds and we don't want the overhead of a job running every few seconds when there may or may not be data to import, that is why the trigger theory works (should !) so well. I can get it to insert data, but cannot get it to update. I have made the users on server A impersonate a user on server B from the security tab within the linked server. Any ideas? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-26 : 07:29:24
|
| If anything the trigger will have more overhead than a job will, especially if the table is being updated often enough that you'd need to schedule a job every few seconds. Also, if there is no data to import, then there is no overhead at all; the job will find there are no new rows to import immediately and will terminate right away. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-26 : 09:55:28
|
| Don't know what you are doing with these databases but have you thought about what happens if you have to restore one?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Cross database Trigger