About a year ago, I started playing around with CLR and my desire to disable xp_cmdshell for my clients.  As a result, I had created the CodePlex project FileSystemHelper.  I blogged about it back then and had meant to get back to it with a number of updates but that never really happened. 

Along the way, Ted Krueger (Blog | @Onpnt) mentioned that he had some updates to add in as well.  Ted’s now a contributor to the project and hopefully between him and I we can keep the project a little more fresh and add in any other functionality that may be helpful.

FileSystemHelper Features

The current functions and stored procedures are:

• Utility.DirectoryCreate Stored procedure that creates a directory based on the @Path value provided.
• Utility.DirectoryDelete Stored procedure that deletes a directory based on the @Path value provided.
• Utility.DirectoryDeleteContents Stored procedure that deletes the contents of a directory based on the @Path value provided.
• Utility.DirectoryList Table valued user-defined function that accepts @Path and @Extension and returns information on the directory. Leaving the @Extension variable blank will return all files in the directory. The table returned provides Name, Directory, Size, DateCreated, DateModified, and Extension.
• Utility.DirectoryMoveTo Stored procedure that moves the contents of one directory to another. The procedure accepts two variables @Path and @Destination. Where files at @Path are moved to @Destination.
• Utility.DirectorySize Function that returns the total size in bytes of all files within a directory and its sub-directories.
• Utility.FileCopy Stored procedure that copies files from one location to another. The procedure accepts two parameters @ExistingFileNamePath and @NewFileNamePath. The existing file is uses the @ExistingFileNamePath and the new file name is submitted with @NewFileNamePath. The procedure will overwrite existing file.
• Utility.FileCreate Stored procedure that creates an empty file. The stored procedure accepts two parameters @Filename and @Path. The parameter @Filename accepts the name of the file created. The parameter @Path accpets the location where the file is created.
• Utility.FileDelete Stored procedure that deletes a specified file. The parameter @Filename accepts the path and filename of the file to be deleted.
• Utility.FileMove Stored procedure that moves a file from one location to another. The stored procedure accepts two parameters @Filename and @FileDestination. The parameter @Filename accepts the name of the existing file. The parameter @FileDestination accepts the location where the file is moved to.
• Utility.FileRename Stored procedure that renames a file. The stored procedure accepts two parameters @FileNewName and @FileOldName. The parameter @FileNewName accepts the path and name of the new file name. The parameter @FileOldName accepts the path and name of the existing file.
• Utility.FileSearch Stored procedure that searches a directory for a string pattern. The stored procedure accepts two parameters @Pattern and @Path. The @Pattern parameter determines the value to search for within the folder. The @Path defines the folder that the procedure will search.
• Utility.FileSearchInternal Stored procedure that searches a file for a string pattern. The stored procedure accepts two parameters @Pattern and @Filename. The @Pattern parameter determines the value to search for within the folder. The @Filename defines the file that the procedure will search.

FileSystemHelper Security

To alleviate concerns with security, this CLR project is built with that thought first and foremost.  Instead of having to enable TRUSTWORTHY and over extend permissions, the library is secured with certificate that limits the scope of access that the project can influence.

If you’ve been looking for a way to get xp_cmdshell turned off in your environment, then give this a try.  It might provide the coverage for those last bits of carry over that you haven’t been able to cover yet.

Related posts:

1. FileSystemHelper SQL Server CLR – Codeplex
2. Can You Dig It? – The Plan Cache
3. Really Search Cache for Execution Plans