Re: Job owned by a non-sysadmin fails to run

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@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C7F5CB7B-970A-4EFF-885C-772ED21ECF0A@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx> wrote in message
news:uVs$ZSQKHHA.2232@xxxxxxxxxxxxxxxxxxxxxxx
- 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@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A7AC10BD-AE8F-4C96-ADE3-1F1603A38D9C@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: SQL account rights
    ... Please advice what is the best, suitable rights rather than domain admin ... Warren Brunk - MCITP - SQL 2005, ... Add it as a login to the SQL Server ... files, or backups, make sure that the service account has Full ...
    (microsoft.public.sqlserver.security)
  • Re: User authentication
    ... There are 2 SQL Server 2005 ... 1 SQL Server 2000 installed on another server ... Windows account instead to run backup jobs. ...
    (microsoft.public.sqlserver.clients)
  • Re: SQL 2000 Server gets hacked
    ... Thank you Beth. ... > placed a strong password on the 'sa' account?) ... Your SQl Service itself shouldn't be running as a ... (SQL Agent requires more, but not SQL Server). ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 Server gets hacked
    ... Thank you Beth. ... > placed a strong password on the 'sa' account?) ... Your SQl Service itself shouldn't be running as a ... (SQL Agent requires more, but not SQL Server). ...
    (microsoft.public.sqlserver.security)
  • Re: Microsoft Search service cannot be administered under the present user error SP3
    ... - Have not modified Administrator account, but i ran the SQL script anyway. ... SQL account is not a local administrator. ... > has this server ever been upgrade from SQL Server 7.0 or is this SQL ...
    (microsoft.public.sqlserver.server)