trigger should delete file... (and does not!)
Posted on 2003-02-25
I want to delete a file, if the according record in my database is deleted (no problems with adding files).
I set up a delete-trigger, which calls the following stored proc (using xp_cmdshell):
CREATE PROCEDURE [dbo].[TP_DELIMAGE]
@SerID AS BIGINT,
@ImgID AS BIGINT
/* Build path of image to be deleted */
DECLARE @sPath sysname
SET @sPath = [..build the path out of other tables.. no problem with that!]
/* build command string to be processed */
DECLARE @sCmd sysname
SET @sCmd= ' DEL C:\' + @sPath + '\' + CAST(@ImgID AS NVARCHAR(256))
/* execute command string */
EXEC master..xp_cmdshell @sCmd, no_output
This all works fine, if I am connected as sa or dbo, if I login as user then I encounter the error:
xpsql.cpp: Error 997 from GetProxyAccount on line 499
I found this has to do with an access problem of xp_cmdshell.
I'm just interested to delete a specific file in a defined area when a record is deleted. Are there any other solutions to do that besides using xp_cmdshell?
How do I have to set the rights on xp_cmdshell to allow users to use that SP?
Any hint is appreciated...