Delete File From TSQL Stored Proc 1

[TheInsider]

Hi,

I'm having difficulty deleting a file from within a stored procedure. I know there are at least two methods of achieving this...

EXEC XP_CMDSHELL 'del C:\temp\myfile.txt'

The above code works if I log into SQL Server using Windows Authentication only and execute the stored proc within management studio. However, if I try to call the stored proc from a C# module using the SQL Server login account that I created, I get an error stating the the user doesn't have adequate permissions to run XP_CMDSHELL.

The second method of deleting a file is

BEGIN
	DECLARE @Result INT
	DECLARE @FSO INT

	EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO OUTPUT
	PRINT CONVERT(VARCHAR, @@ERROR) + ' : ' + CONVERT(VARCHAR, @Result)
	EXEC @Result = sp_OAMethod @FSO, 'DeleteFile', NULL, 'C:\temp\myfile.txt', 1
	PRINT CONVERT(VARCHAR, @@ERROR) + ' : ' + CONVERT(VARCHAR, @Result)
	EXEC @Result = sp_OADestroy @FSO
	PRINT CONVERT(VARCHAR, @@ERROR) + ' : ' + CONVERT(VARCHAR, @Result)
END

Unfortunately, the latter code never deletes the file. The output from the print statements is:

0 : 0
0 : -2146828218
0 : 0

(1 row(s) affected)

I don't know what -2146828218 is supposed to be, but it obviously indicates the deletion failed. It appears that even using my Windows Authentication login, I don't have permission to use the FileSystemObject to delete files.

If I have a SQL Server login account called "webserver" that I use to call stored procs from C#, what's the easiest way to delete files from TSQL... using the least amount of tweaking security permissions?

And yes, I do want to do this deletion from TSQL and not ASP.NET / C#.

TIA

 

[SQLDenis]

>> I get an error stating the the user doesn't have adequate permissions to run XP_CMDSHELL.

and with good reason since it is dangerous

are you running 2005?

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TheInsider]

Thanks for the reply. Yes, I'm running 2005. I realize that it's a dangerous command; however, I'm only developing an internal intranet site, so it's extremely unlikely that any employees will be attempting SQL-injection or buffer overflow attacks on the web site. I'll check out that link and see if I can get it working.

 

[TheInsider]

OK, assuming I wanted to go with the FileSystemObject DeleteFile method, how would I get rid of the following error messages?

Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 1
The EXECUTE permission was denied on the object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.

I tried

USE master
GRANT EXECUTE ON sp_OACreate TO webserver
GRANT EXECUTE ON sp_OAMethod TO webserver
GRANT EXECUTE ON sp_OADestroy TO webserver

and got:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

 

[SQLDenis]

you gant grant execute on that. Only members of the sysadmin fixed server role can execute sp_OACreate.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

there is also xp_delete_file...


--------------------
Procrastinate Now!

 

[gmmastros]

Just to avoid confusion....

xp_delete_file does [!]NOT[/!] exist for [!]SQL 2000[/!].


I realize...

Yes, I'm running 2005.

I put this comment here for the benefit of other readers that are still using sql 2000.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheInsider]

Thanks to everyone who responded. I had to add the give the SQL Server account permission to the folder in order to delete files.

 

[TheInsider]

Just out of curiosity, what is the syntax for xp_delete_file to delete a file named...say "c:\temp\myfile.txt"? I searched and searched but couldn't get a list of the parameters. All the examples I found on Google were for deleting log files based on dates.