August 29, 2006 at 11:12 am
I have DB A and a SP with parameters on it.
I have DB B and a Table with a Trigger. The trigger needs to execute the SP. A value is passed to the SP and another value is returned. The returned value is updated in the Table.
Q: I want to run the SP as different user than the one logged into DB B. Users of DB B does not have permission on DB A. What is the best way of doing this? Both DBs are on the same server. What if it was on different server.
Thanks in advance.
August 29, 2006 at 1:45 pm
In SQL Server 2005 you can use EXECUTE AS.
August 29, 2006 at 2:01 pm
Thank you. This is SQL 2k. Can I still use it?
August 30, 2006 at 12:10 am
For SQL2K You could have the user initiated trigger insert the value(s) used for the stored procedure parameters into a queue table with a GETDATE() column then create another stored procedure to process the queue table to execute the sp and perform the update. This new sp would be run from a scheduled SQL Agent Job configured for the desired user account. The latency of this operation would be determined by the Job's schedule.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply