Well, the short answer is you don't.  The modified answer is that you can use the code listed in this post. The longer answer is that you can use xp_cmdshell to do this (Which is what the stored procedure in this post does),  and must be either a member of the sysadmin account, or granted execution, say to a new role, and users can be placed in that role. 

There is a caveat to that.  From Books Online:

Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

Important  If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

Also, the long and short of it is, that having to build it, then to parse out the data, and to worry about security measures, I thought it might be a good idea just to create a stored procedure.  Now I'm sure someone may have done this already, but I thought I'd give it a shot.  This makes looking at your directories as easy as issuing the command EXEC Dir 'C:\*.*'.  Well you say, why not just do EXEC xp_cmdshell 'Dir C:\*.*'. Well for one, you won't have to grant your developers access to xp_cmdshell, so you gain more control.  A developer could reak havoc with your server. I've seen it.  Also, the information is now in a usable format.  Your developers can now call this stored procedure and place the results in to a table, or, (since it's all done with smoke and mirrors), they can access the underlying directory table.   As an afterthought, there are several commands that can be turned in to this type of functionality, and you can specialize them to “extend“ dos commands.  Currently I'm working on an archiving sproc that will make an archive directory for a directory supplied.  The idea is that when I'm done with all the files in my processing directory, I will move everything to the archive, so as to clean out the directory for the next processing cycle.  I'll post that when it's done.

One additional caveat is that this is Windows 2000 Server and up.  Windows NT produced different ouput results from the dir dos command.  If you need the old structure, let me know and I can post it.   I know there's away around this, I'm just not sure at the moment on how to do this.  If anyone remembers, I'd like to know.  Again thanks for reading.

 Here's the code, and good luck. 

CREATE TABLE [dbo].[Directory_Contents_Stage] (
   [dir]  varchar(255)
 , [dir_output]  varchar(255)
)
GO

CREATE TABLE [dbo].[Directory_Contents] (
   [dir]  varchar(255)
 , [Create_Time] datetime
 , [File_Size]  int
 , [File_Name]  varchar(255)
 , [Struct_Type] char(9)
)
GO

CREATE PROC Dir(@path varchar(2000), @Supress int = 0)
AS

SET NOCOUNT ON
TRUNCATE TABLE Directory_Contents_Stage

    DECLARE @cmd varchar(4000)
     SELECT @cmd = 'Dir "' + @path + '"'

INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the file specified.')
    BEGIN
         PRINT 'The system cannot find the file specified.'
         GOTO Dir_Error
    END

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'File Not Found')
    BEGIN
         PRINT 'File Not Found'
         GOTO Dir_Error
    END         

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the path specified.')
    BEGIN
         PRINT 'The system cannot find the path specified.'
         GOTO Dir_Error
    END

     UPDATE Directory_Contents_Stage SET [dir] = @path

DELETE FROM Directory_Contents WHERE [dir] = @path

INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , CONVERT(int,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size]
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
     , [dir]
     , 'FILE'
      FROM  Directory_Contents_Stage
     WHERE  SUBSTRING(dir_output,1,1) <> ' '
       AND (SUBSTRING(dir_output,1,1) <> ' '
       AND  SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62))