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
EXEC sp_getdir '\\server\share' |