Hello.

Below is the setup of our environment to run the DTS job. This setup will
work numerous SQL Server 2000 machines, 2 Developer Editions, and 2 other
Enterprise Editions. The only one it cannot run on is a Enterprise Edition
box (our Production, bummer). We have tested on other editions of SQL Server
up to the patch level (8.00.818) and beyond.

Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )


Setup:

One database. One stored procedure in that database. The stored procedure
starts a job. Here is the procedure.

CREATE procedure stp_ExecuteDTS
as
declare @Jobname varchar(30)
SET @Jobname = 'MyDTSJob'
exec msdb.dbo.sp_start_job @Jobname
GO


The user is a dbo of the database. The job is owned by the same user. The
user is not a SysAdmin. The SQL Server SQL Agent has a Non SysAdmin Job Step
Account specified. Actually the same account which is an administor on both
boxes (which also makes them SA on each box) is used for Test and Production.
However when running this on production this is the error message we get.

Executed as user: MyDomain\sqlproxyacc. The process could not be created for
step 1 of job 0x6E5CE2C4BE2DE34E93EDA370C364B553 (reason: A required
privilege is not held by the client). The step failed.

The step is a CmdExec step which is below:

"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S
"PRODSERVER" /N "MyDTSJob" /W "0" /E


Any help on this would be appreciated. Thanks.
--
Mike Logan

More Information:

The job has an Operating System Command (CmdExec) step. Using the proxy
account to run the job, we get the "A required privilege is not held by the
client" failure. When we don't use the proxy account we get the following
error:

"Non-SysAdmins have been denied permission to run CmdExec job steps. The
step failed."

When the job is owned by the proxy account and run by the proxy account
user, the job successfully runs. When the app account user has sa
privileges, the job runs successfully. We would like to run the job with the
proxy account and have the app account be a dbo with no other privileges.


--
Mike Logan

Hello Mike,

This can occur because the SQL agent server account does not have the
required privileges so as to verify the password of the SQLAGENTCMDEXEC
account. This account is used by non-sa users when they execute cmdexec or
xp_cmdshell jobs.


This problem may have occured because the SQL Server Agent account was
changed in Control Panel -> Services.


For all the required privileges to be assigned to this account and to the
SQL Server Account the account must be changed in Enterprise Manager. Even
if only the password is changed this must be done in Enterprise Manager.

The four required privileges of the SQL Server Account are:



1 - Act as part of the Operating System

2 - Increase Quotas

3 - Log on as a Service

4 - Replace a process level token

Hope this is helpful.


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------