I have 2 servers: one is WebServer, the other is DBServer
SQL Server is installed on DBServer, I created a DTS (exporting from table
to Excel file), the destination file is on WebServer( \\WebServer\aaa.xls). I
successfully run the DTS in Enterprise Manager, but failed when running by
Stored Procedure (destination file is a parameter of Stored Procedure, when I
change the destination to '\\DBServer\aaa.xls', it running OK).
Any expert could tell me why? Security problem? URGENT!!!

The likely cause is security. Posting the error message would help identify
the cause.

If the proc is begin run by a sysadmin role member, then the package will
run under the SQL Server service account. That account needs permissions to
the \\WebServer share. Note that the service account must be a domain
account rather than local system in order to access network resources.

If the proc is run by a non-sysadmin role member, then you need to allow
non-sysadmin users to execute CmdExec steps and configure the proxy account.
You can configure this using Enterprise Manager under Management-->SQL
Server Agent-->Properties-->Job System.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Levin" <Levin (AT) discussions (DOT) microsoft.com> wrote