testing this further, the access denied is due to not having the modify permission on folder. the xp_cmdshell was run through query analyser (connect with admin login) and dir *.* command works. therefore looking at BOL i expect that this applies: "When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running".
Changing folder security to allow everyone to modify files allows xp_cmdshell to copy the file to a subfolder. however i want to restrict users to read only access.
to accomplish this i think i should:
1. add the account that sql server service runs under to the folder
2. give that account the modify permissions for the folder
to allow DTS schedule, repeat the above for the account that sql server agent runs under - BOL says "When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account".
correct me if i am wrong but i don't think i can add the local system account to the folder.
therefore should i create a new account for sql server and sql server agent to run under first? and then add the account to the folder with neccessary permissions. thanks for any guidance.