get file names from folder

get file names from folder

• GavH

  Hi Arnie,

  You're right, xp_cmdshell assumes high level of server security, you have to use SQL Server configuration tools to set that security. But if he will write this procedure in VB or C# he will need to get same security anyway. My point is that without access to server resources he won't get this information, regardless of method he is using.

  
  

• pepellini

  You can use proxy accounts. But the xp_cmdshell approach has lot of drawbacks. Below are major ones.

  1. It is hard to return table results efficiently. With SQLCLR, you can write TVFs that return the results. This allows great flexibility in terms of composing queries, reusability etc

  2. xp_cmdshell does allow more things than just executing DIR. Granted this is governed by the permissions of the account under which it is being run. But there are only so many things you can protect at the OS level and allowing any arbitrary command to be executed via xp_cmdshell is a problem. With SQLCLR approach, the external access is restricted only to specific assemblies and operations are restricted to the assembly logic. You are prone to SQL injection attacks or worse but it is probably harder to exploit.

  
  
  

• Todd Raymond

  You can create a CLR FUNCTION that can be used in T-SQL code.

  See:

  Function -SQL CLR to Return File List
  C# - http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx
  VB - http://blogs.msdn.com/sqlclr/archive/2005/05/20/420623.aspx

  
  
  

• edukulla

  Mark Shvarts wrote:

  exec master.dbo.xp_cmdshell 'dir c:\' -- put here your directory name

  The issue with using xp_cmdshell is that it requires you to provide the user a relatively high level of security access, and has relatively unfettered access to all server resources, and you may not wish to make that functionality available to any users (other than administrators).

  
  
  

• Jack Wheeler

  Actually same thing can be done by using proxy account ##xp_cmdshell_proxy_account##.

  What this means is that users will be able to use xp_cmdshell only in context of a stored procedure where they are granted privileges.

  
  

• Lili Gao

  Another way to do this in SQL Server 2000/2005 is to use distributed queries. You can basically use the indexing services to index the directories and query those from SQL Server directly. See link below for some information:

  http://msdn2.microsoft.com/en-gb/library/aa172902(SQL.80).aspx

  Search in MSDN for more details on how to setup linked servers and queries against indexing services.

  
  
  

• sigme

  However, if a procedure or function is created in .NET CLR, once created, permissions can be given to users to use that procedure or function, and they will be limited to only what that procedure or function can accomplish.

  However, once permission is given to use xp_cmdshell, the users have virtually unfettered ability to do many things ( such as FORMAT C:\ ) other than the original intended action.

  Using a .NET CLR procedure or function allows you to craft functionality that is severely constrained to the single task desired.

  
  
  

• jwellsntr

  Try this:

  create table #files (files varchar(max))
  go

  insert into #files
  exec master.dbo.xp_cmdshell 'dir c:\' -- put here your directory name

  select * from #files
  go
  

  Make sure that SQL Server account you're running is having access to the server ( not your local box !) file system.

  Then you can delete garbage entries and use substring function to retrieve your information.

  
  
• get file names from folder