Technical Article

Get contents of a directory/share to a table

,

Following on from todays QOD (on xp_cmdshell permissions) I thought this might be useful.

Takes a directory or UNC path and returns the contents as a table. 

eg:
exec sp_getdir '\\mypc\c$'

returns:
datestring timestring directory   filesize    nameoffile
---------- ---------- ----------- ----------- ----------------------
18/07/2003 10:45      1           NULL        Documents and Settings
18/07/2003 11:02      1           NULL        Program Files
10/07/2003 17:03      1           NULL        WINNT

It handles the error mentioned in the QOD with a useful message.

eg:
exec sp_getdir '\\mypc\c$' (where I'm not sysadmin)

might return:
Current user's login is NOT a member of the sysadmin role
Non sysadmin executions of xp_cmdshell currently run as: MYDOMAIN\AUSER
You can change this with xp_sqlagent_proxy_account N'SET', , ,

create proc sp_getdir (@networkpath varchar(1000)) as
begin
set nocount on
create table #temp([output] varchar(8000))
declare @cmdstr varchar(1000)
select @cmdstr = 'dir ' + @networkpath + ' /A:D /A:S /A:H /A:R /A:A /-C /N /4 '

insert #temp
exec master.dbo.xp_cmdshell @cmdstr

select left(t.[output],10) as datestring, substring(t.[output],13,5) as timestring,
case substring(t.[output],26,3) when 'DIR' then 1 else 0 end as directory ,
case substring(t.[output],26,3) when 'DIR' then null else cast(ltrim(substring(t.[output],20,19)) as int) end as filesize,
substring(t.[output],40,1000) as nameoffile
from #temp as t where t.[output] like '[0-9][0-9]%'
if @@error <> 0 or not @@rowcount > 0 goto doh

goto done
doh:

if exists(select * from #temp where rtrim(ltrim([output])) = 'The network path was not found.')
print @networkpath + ' was not found.'
else
begin
if exists(select * from #temp where rtrim(ltrim([output])) = 'Logon failure: unknown user name or bad password.')
begin
print 'Login failure to ' + @networkpath
if is_srvrolemember ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role' + char(10) + 
'The account MSSQL runs under does not have access to ' + @networkpath
else 
begin
print 'Current user''s login is NOT a member of the sysadmin role'
declare @Domain sysname, @Username sysname
create table #temp2(Domain sysname, Username sysname)
insert #temp2
exec master.dbo.xp_sqlagent_proxy_account N'GET'

select @Domain = t.Domain, @Username = t.Username from #temp2 t
print 'Non sysadmin executions of xp_cmdshell currently run as: ' + isnull(@Domain + '\' + @Username, 'No user set')
drop table #temp2
print 'You can change this with xp_sqlagent_proxy_account N''SET'', <domain>, <username>, <password>'
end
end
else
select * from #temp
end

done:
drop table #temp
set nocount off
end
go

Rate

Share

Share

Rate