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

- 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: DTS Package fails when Scheduled
    ... Make sure SQL Server Agent account has the correct rights/permissions. ... scheduled job under this context, I still received the error, even though I ...
    (microsoft.public.sqlserver.dts)
  • Re: Problems with SQL Srv. Agent and Proxy Account
    ... I have tried with both the sa account and with Use Windows ... Authentication on the SQL Server AGent Connection tab. ... >>rights recording to INF: Reset Proxy and the ...
    (microsoft.public.sqlserver.security)
  • Re: cant find directory when package scheduled as job
    ... the batch file to map the drive. ... from the DTS designer but fails when it is scheduled as a SQL Server Agent ... under my windows account and the packages executes correctly when run via ...
    (microsoft.public.sqlserver.dts)
  • Re: Problems with SQL Srv. Agent and Proxy Account
    ... You need to go to the General Tab of the SQL Server Agent ... >I have tried with both the sa account and with Use Windows ...
    (microsoft.public.sqlserver.security)
  • 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)