October 26, 2007 at 7:21 am
I have a developer who would like to use either one of the 2 processes below on a sql2000 instance running many apps.
1:sp_OACreate, sp_OAMethod and sp_OADestroy
2 xp_cmdshell
What he needs to do is if an event happens write a file and then his app will trigger off of it another event.
That is all I know and he is asking me to grant exec permissions to the app login id
My initial response is no but I am not certain what best practices are and do not truly understand step 1.
Also I told him this file must not land on the dbase server and if I am correct I think thats where it will try to put it.
Any input would be helpful.
December 13, 2007 at 10:13 am
When your developer says "An event" does he mean an event in a database?
Does he need it to be synchronous i.e the instant the event happens, generate a file? What does he want in the file?
What is he going to do when he gets it?
I've had requirements to allow a developer to trigger a job or DTS package but the way I have achieved it is to create table containing a single record with a single BIT field.
I have a background job that runs every 30 seconds that checks for the existence of a record with the flag set to 1. If the flag is set then the DTS package is run.
The advantage of this approach is that the developer only needs the rights to call a single stored procedure to set the value in the table. The actual job to create files is running under the SQLSERVERAGENT account and therefore has sufficient rights to do what it needs to do.
I have always avoided sp_OACreate on production boxes as memory leaks on production boxes are a recipe for down time. Also, you have to be absolutely sure that objects are destroyed at least as fast as they are created.
December 14, 2007 at 3:33 am
In SQL 2000 you can grant anyone permissions to use the SP_OA routines.
In SQL 2005 and 2008 they can only be run by someone with Sysadmin authority. This is because the mssqlsystemresource database holds permissions to objects in master, and you have no way of changing what is in mssqlsystemresource. IMHO this is broken by design.
If you plan to let non-Sysadmin people run the final process, and you think you may in the future upgrade to SQL 2005 or 2008, then you will have to change what you do.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply