Here is a script I use occasionaly. This is helpful to me when
I want to create custom role permissions, mapped to a database user or
just user credentials stored in the database, for files in a project or
website. I use this to load files into the database, and then
create the cross-reference for granting or denying access. Credit
goes to Neil Jacobson.
This stored procedure reads the file names from a directory and
stores just the names in a given table. It returns also the number of
files present. The sp uses xp_cmdshell and so permissions are
restricted to sysadmins and SQLAgentCmdExec. You can test with the
following: Create Table ##tmp2 ( fldx nvarchar(255) ) Declare @FilePath
nvarchar(2048) , @tblName nvarchar(255) , @FileCount int Set Nocount on
Select @FilePath = 'C:\Test1' , @tblName = '##tmp2' , @FileCount = 0
EXEC master.dbo.sp_DirFiles @FilePath , @tblName , @FileCount OUTPUT
Select * From ##tmp2 select 'Filecount = ', @FileCount drop table
##tmp2
