Sorry if this has been answered elsewhere. A quick perusal of the FAQs and a keyword search where fruitless.
Given:
- A db user with limited permissions (User_A).
- A table into which that user would like to insert records (Raw_Data).
- User_A has select, insert, update, delete permissions on the Raw_Data table.
- A second table of values derived from the raw data on which User_A does not have 'writer' permissions named Derived_Values.
- User_A is a piece of Perl code that connects and inserts records.
Originally, over a period of weeks, User_A successfully inserted records in the Raw_Data_Table. Later, in order to automatically create the derived values, we put a trigger on the the Raw_Data table to fire a stored procedure which calc'd the derived values and inserted them in to the second table. Since User_A does not have write permissions on the Derived_Values table, the trigger fails (and appropriately so).
I logged on as 'sa' and built a stored procedure (SP_1) used to insert data into the RAW_DATA table. I granted User_A 'execute' permissions on the procedure. I expected that a stored procedure owned by 'sa' would be able to insert records into the Raw_Data table and successfully fire the trigger. It does not work. If I execute SP_1 as 'sa', it works fine. However, if I log on as User_A and execute SP_1 to try to insert a record into the Raw_Data table, I get, "EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'." Apparently, even though the stored procedure (SP_1) is owned by 'sa', it runs as the db user that executes it. In this case, 'User_A'.
The question:
Is there a 'run as' trick or something similar that I can build into the stored procedure (SP_1) to do the inserts into the Raw_Data table that would allow the trigger to update the downstream tables when User_A does the work?
I don't see anything in the Transact_SQL Reference. You guys were my next stop.
Thanks in advance for any contributions.
'hope this helps
If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.
Given:
- A db user with limited permissions (User_A).
- A table into which that user would like to insert records (Raw_Data).
- User_A has select, insert, update, delete permissions on the Raw_Data table.
- A second table of values derived from the raw data on which User_A does not have 'writer' permissions named Derived_Values.
- User_A is a piece of Perl code that connects and inserts records.
Originally, over a period of weeks, User_A successfully inserted records in the Raw_Data_Table. Later, in order to automatically create the derived values, we put a trigger on the the Raw_Data table to fire a stored procedure which calc'd the derived values and inserted them in to the second table. Since User_A does not have write permissions on the Derived_Values table, the trigger fails (and appropriately so).
I logged on as 'sa' and built a stored procedure (SP_1) used to insert data into the RAW_DATA table. I granted User_A 'execute' permissions on the procedure. I expected that a stored procedure owned by 'sa' would be able to insert records into the Raw_Data table and successfully fire the trigger. It does not work. If I execute SP_1 as 'sa', it works fine. However, if I log on as User_A and execute SP_1 to try to insert a record into the Raw_Data table, I get, "EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'." Apparently, even though the stored procedure (SP_1) is owned by 'sa', it runs as the db user that executes it. In this case, 'User_A'.
The question:
Is there a 'run as' trick or something similar that I can build into the stored procedure (SP_1) to do the inserts into the Raw_Data table that would allow the trigger to update the downstream tables when User_A does the work?
I don't see anything in the Transact_SQL Reference. You guys were my next stop.
Thanks in advance for any contributions.
'hope this helps
If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.
