Job owned by a non-sysadmin fails to run

Hi,
The preblem has been already discussed but none of answers help me.
I have a SQL Server 2000 SP4. Users that are not sysadmins create jobs for
SQL Server Agent. These jobs consist of a single CmdExec step.
As advised in many posts I created a Proxy SQL Server Agent account
(sqlproxy) but this did not help me, the jobs still fail to run. This
account is a windows account. I made this account belong to the sysadmins
role of SQL Server.
Both SQL Server and SQL Server Agent run under a special account
(sqlservice).
I added the account sqlservice to Administrators as advised in the article
http://support.microsoft.com/kb/833559 and even added to Administrators the
account sqlproxy, although the article states I did not have to.
The message I get in EventLog is like below:
-- message start
SQL Server Scheduled Job '<job name>' (0xEFC686299E5B9249957CC5FCF5C782C4) -
Status: Failed - Invoked on: 2006-12-18 12:06:06 - Message: The job failed.
The Job was invoked by User <domain>\<user>. The last step to run was step
1 (<step name> ).
-- message end
The command of the CmdExec step runs fine if I login as user as well as
sqlproxy.
I set the output file in advanced properties of the CmdExec step but did not
see in that file anything. Looks like the job does not start at all.
When I add user acocunt to the group Administrators the job runs
successfully, but this is definitely not an option.
I would appreciate any help as I run out of ideas already.Try changing the owner of the job to SA or another sysadmin.
Ivan Gerken wrote:
> Hi,
> The preblem has been already discussed but none of answers help me.
> I have a SQL Server 2000 SP4. Users that are not sysadmins create jobs for
> SQL Server Agent. These jobs consist of a single CmdExec step.
> As advised in many posts I created a Proxy SQL Server Agent account
> (sqlproxy) but this did not help me, the jobs still fail to run. This
> account is a windows account. I made this account belong to the sysadmins
> role of SQL Server.
> Both SQL Server and SQL Server Agent run under a special account
> (sqlservice).
> I added the account sqlservice to Administrators as advised in the article
> http://support.microsoft.com/kb/833559 and even added to Administrators th
e
> account sqlproxy, although the article states I did not have to.
> The message I get in EventLog is like below:
> -- message start
> SQL Server Scheduled Job '<job name>' (0xEFC686299E5B9249957CC5FCF5C782C4)
-
> Status: Failed - Invoked on: 2006-12-18 12:06:06 - Message: The job failed
.
> The Job was invoked by User <domain>\<user>. The last step to run was ste
p
> 1 (<step name> ).
> -- message end
> The command of the CmdExec step runs fine if I login as user as well as
> sqlproxy.
> I set the output file in advanced properties of the CmdExec step but did n
ot
> see in that file anything. Looks like the job does not start at all.
> When I add user acocunt to the group Administrators the job runs
> successfully, but this is definitely not an option.
> I would appreciate any help as I run out of ideas already.|||This quick solution is not an option.
Jobs are created from an external application (Business Desk of Commerce
Server 2002). A created job is owned by the user logged in to the
application (using win integrated authentication).
I definitely don't want to give users administrative privileges.
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1166704870.838450.230270@.f1g2000cwa.googlegroups.com...
> Try changing the owner of the job to SA or another sysadmin.
>|||For debugging, try temporarily changing the job owner to 'sa' as PSPDBA
suggested. If the job still fails, then the problem is related to running
as a job rather than a security issue.
Exactly what does the CmdExec step do? Are mapped drives accessed?
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:uE$3GeRJHHA.420@.TK2MSFTNGP06.phx.gbl...
> This quick solution is not an option.
> Jobs are created from an external application (Business Desk of Commerce
> Server 2002). A created job is owned by the user logged in to the
> application (using win integrated authentication).
> I definitely don't want to give users administrative privileges.
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1166704870.838450.230270@.f1g2000cwa.googlegroups.com...
>|||Looks like I have a problem with CmdExec jobs in general.
I changed the step command to "dir c:\temp" and it ran fine when owned by an
admin but failed when owned by a user. In case of being owned by a user even
the output file was not created. The folder c:\temp has "full control"
permission granted to everyone.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:42FF0C9C-4AFC-42AF-9464-0931A1DDE13B@.microsoft.com...
> I'm starting to run out of ideas. Do you have any CmdExec job steps that
> successfully run as non-sysadmin users or is it just dmlrun.exe that has
> the problem?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||Lets make sure I have the relevant details right since so much has been
discussed in this thread:
- SQL Server service and SQL Server Agent service run under the same
account
- The account is a member of the local administrators group
- xp_cmdshell runs fine when involed by non-sysadmins
- CmdExec jobs fail for jobs owned by non-sysadmins
What I find strange is that xp_cmdshell works but CmdExec doesn't. I can
see how this might be the case if you used different service accounts and
the SQL Agent service account lacked the advanced user rights (e.g. 'act as
part of the operating system' and 'replace a process-level token') that are
needed to switch security context to the proxy account.
Can you double-check to ensure the same service account is used for SQL
Server and SQL Server Agent services? If you have made changes to service
account security, have you since restarted the service? In some cases, a
server restart in needed in order for security changes to fully take affect.
Happy Holidays
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:%239rDNPAKHHA.2236@.TK2MSFTNGP02.phx.gbl...
> Looks like I have a problem with CmdExec jobs in general.
> I changed the step command to "dir c:\temp" and it ran fine when owned by
> an admin but failed when owned by a user. In case of being owned by a user
> even the output file was not created. The folder c:\temp has "full
> control" permission granted to everyone.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:42FF0C9C-4AFC-42AF-9464-0931A1DDE13B@.microsoft.com...
>|||- SQL Server service and SQL Server Agent service run under the same
account
Yes, referred to earlier as sqlservice. However, the services MSSEARCH,
MSSQLServerADHelper, MSSQLServerOLAPService run under Local System (I think
it hardly matters but just in case).
- The account is a member of the local administrators group
Yes, plus OLAP Administrators and Users.
- xp_cmdshell runs fine when involed by non-sysadmins
Yes. User account is a member of Users and Remote Desktop Users.
- CmdExec jobs fail for jobs owned by non-sysadmins
Yes, even after restarting both MSSQLSERVER and SQLSERVERAGENT.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:A7AC10BD-AE8F-4C96-ADE3-1F1603A38D9C@.microsoft.com...
> Lets make sure I have the relevant details right since so much has been
> discussed in this thread:
> - SQL Server service and SQL Server Agent service run under the same
> account
> - The account is a member of the local administrators group
> - xp_cmdshell runs fine when involed by non-sysadmins
> - CmdExec jobs fail for jobs owned by non-sysadmins
> What I find strange is that xp_cmdshell works but CmdExec doesn't. I can
> see how this might be the case if you used different service accounts and
> the SQL Agent service account lacked the advanced user rights (e.g. 'act
> as part of the operating system' and 'replace a process-level token') that
> are needed to switch security context to the proxy account.
> Can you double-check to ensure the same service account is used for SQL
> Server and SQL Server Agent services? If you have made changes to service
> account security, have you since restarted the service? In some cases, a
> server restart in needed in order for security changes to fully take
> affect.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP|||> Yes, even after restarting both MSSQLSERVER and SQLSERVERAGENT.
Have you restarted the server since you added the sqlservice account to the
local Administrator's group? Although not normally required, I've seen
occasions where a restart was needed to pickup the group membership change.
BTW, are there any related messages in the SQL Agent log files?
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:uVs$ZSQKHHA.2232@.TK2MSFTNGP02.phx.gbl...
>- SQL Server service and SQL Server Agent service run under the same
>account
> Yes, referred to earlier as sqlservice. However, the services MSSEARCH,
> MSSQLServerADHelper, MSSQLServerOLAPService run under Local System (I
> think it hardly matters but just in case).
> - The account is a member of the local administrators group
> Yes, plus OLAP Administrators and Users.
> - xp_cmdshell runs fine when involed by non-sysadmins
> Yes. User account is a member of Users and Remote Desktop Users.
> - CmdExec jobs fail for jobs owned by non-sysadmins
> Yes, even after restarting both MSSQLSERVER and SQLSERVERAGENT.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:A7AC10BD-AE8F-4C96-ADE3-1F1603A38D9C@.microsoft.com...
>|||Dan,
Many thanks for a great hint! I checked SQL Agent log and found there the
following message:
[136] Job <job name> reported: Warning: cannot write logfile
c:\temp\dmout.txt. Error 5 : Access is denied
Then, after I have cleared the "Output file" box the job executed
successfully. So the problem seems to be solved.
However, I find this error very odd because full control is granted to
everyone on c:\temp
Many thanks for your help!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C7F5CB7B-970A-4EFF-885C-772ED21ECF0A@.microsoft.com...
> Have you restarted the server since you added the sqlservice account to
> the local Administrator's group? Although not normally required, I've
> seen occasions where a restart was needed to pickup the group membership
> change.
> BTW, are there any related messages in the SQL Agent log files?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Gerken" <testivan@.waterproof.nl> wrote in message
> news:uVs$ZSQKHHA.2232@.TK2MSFTNGP02.phx.gbl...
>|||I'm glad you were able to get it sorted out. I'm sorry I didn't suggest
checking the log earlier.

> However, I find this error very odd because full control is granted to
> everyone on c:\temp
The error 5 can be caused by the file being used by another process or that
the read-only attribute is set.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:eLhoAacKHHA.3424@.TK2MSFTNGP02.phx.gbl...
> Dan,
> Many thanks for a great hint! I checked SQL Agent log and found there the
> following message:
> [136] Job <job name> reported: Warning: cannot write logfile
> c:\temp\dmout.txt. Error 5 : Access is denied
> Then, after I have cleared the "Output file" box the job executed
> successfully. So the problem seems to be solved.
> However, I find this error very odd because full control is granted to
> everyone on c:\temp
> Many thanks for your help!
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:C7F5CB7B-970A-4EFF-885C-772ED21ECF0A@.microsoft.com...
>