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

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@xxxxxxxxxxxxx> wrote in message news:eLhoAacKHHA.3424@xxxxxxxxxxxxxxxxxxxxxxx
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: Error 15401 using sp_grantlogin (not addressed by current KB articles)
    ... Restarting Windows 2000 resolved the problem for this particular account, ... confused when it sees a duplicate SID. ... > One way to get SQL Server to agree with the renamed NT ... > Preview (to ensure the script was created), ...
    (microsoft.public.sqlserver.security)
  • Re: SharePoint V3 Install Error
    ... But it our case it had to do with Group Policies that forbid the account of ... WSS FAQ:www.wssv3faq.com/wss.collutions.com ... Event Source: WindowsSharePointServices3Search ... whatever you are installing WSS as sufficient rights to the SQL Server ...
    (microsoft.public.sharepoint.windowsservices)
  • RE: Problems with WebParts
    ... to a database called aspnetdb. ... > The connection string specifies a local SQL Server Express instance using a ... > server account must have read and write access to the applications directory. ... > This is necessary because the web server account will automatically create ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Cannot connect to Query Analyzer
    ... For Query Analyzer, I tried replacing the file as you suggested but had the ... same results (Enterprise Manager starts up fine, ... I created an account on my laptop and changed SQL ... Try replacing the MMC app for SQL Server from the original ...
    (microsoft.public.sqlserver.connect)
  • Problems with WebParts
    ... The connection string specifies a local SQL Server Express instance using a ... database location within the applications App_Data directory. ... server account must have read and write access to the applications directory. ... logged-in user needs the dbcreator privilege in the appropriate SQL Server ...
    (microsoft.public.dotnet.framework.aspnet)