To let domain users schedule their DTS jobs on SQL 2000
server, what kind rights or permissions I need to set for
SQL Agent Proxy Account? Does Proxy Account need to belong
to domain admin group?

Thanks in advance.


-George

In article <007601c3aad7$357163e0$a401280a (AT) phx (DOT) gbl>, George
<TST_MC (AT) hotmail (DOT) com> writes
The domain proxy account should not be a member of the domain admin
group, as one purpose of the proxy account is to be of a lower privilege
than that used for the SQL Server service. This does not mean o say it
is a problem if it is, rather it goes against the idea of using the
proxy account properly.

To call DTSRUN there are no special privileges needed. If using
integrated security either load the package or within the package then
you will need to take this into account however and ensure the proxy
account can connect to SQL with the correct level of access to perform
these operations.

From Books Online -
"SQL Server Agent proxy accounts allow SQL Server users who do not
belong to the sysadmin fixed server role to execute xp_cmdshell and own
SQL Server Agent jobs. The administrators can assign appropriate
security permissions to the proxy account to control the ability of
these jobs to access resources in the network."

So nothing special, just give it what it needs to do the jobs you
require.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren;

Thank you for the explanation.
My domain users can create DTS packages and manually run
the packages on SQL server 2000. But when they create jobs
to run their packages, the jobs are failed. The errors
show "cannot access server". I do not want to grant the
users as sa. Does SQL Agent Proxy Account can help?
Thank you for your recommendation.

-George

In article <065701c3ab15$67e43da0$a101280a (AT) phx (DOT) gbl>, George
<tst_mc (AT) hotmail (DOT) com> writes
You need a proxy account to allow users to run CmdExec job steps, so
this must be done already. Back to my original post-

<>
If using integrated security either load the package or within the
package then you will need to take this into account however and ensure
the proxy account can connect to SQL with the correct level of access to
perform these operations.
</>

Are you trying to access a server using integrated security? If so it is
obvious that the proxy account cannot make an integrated connection.

Find the source of the error to see what is causing it, then consider
the permissions required bearing in mind the execution context (Proxy
account) and location (on the SQL Server itself).

The most common issues with scheduled packages are around
permissions due to the execution context and the location of execution-

INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?kbid=269074

Support WebCast: Data Transformation Services Package Scheduling - An
Inside Look
http://support.microsoft.com/default...s/webcasts/wc1
02301/wcblurb102301.asp

I would also recommend you turn on logging within the package, either to
and error file or to a SQL Server (SQL 2000 Only) to get as much
information as possible.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org