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

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@xxxxxxxxxxxxx> wrote in message news:%239rDNPAKHHA.2236@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:42FF0C9C-4AFC-42AF-9464-0931A1DDE13B@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: SPN Requirement
    ... The service account for SQL Server needs to be a domain ... account to allow Write Public Information rights but I'd ... The SQL Network Interface library could not register the Service Principal ... Name for the SQL Server service. ...
    (microsoft.public.sqlserver.security)
  • Re: Question about "Distribution clean up: distribution" Job
    ... I granted the SQL Server Service Account "Full Control" over ... the Snapshot Folder and the job has been running successfully ever since. ... Then I went into the job step and copied the command it was ...
    (microsoft.public.sqlserver.replication)
  • Re: xp_cmdshell right for non sysadmin
    ... You can get the error when the SQL Server service account ... does not have the necessary permissions to change security ...
    (microsoft.public.sqlserver.server)
  • Re: Question about "Distribution clean up: distribution" Job
    ... I am guessing that the SQL Server Agent service account is not what ... rights to remove folders under P:\ReplData. ... Then I went into the job step and copied the command it was ...
    (microsoft.public.sqlserver.replication)
  • Cannot Use Non-Administrator Account to Start SQL Server and Force Encryption
    ... I changed the service account of a named instance (product ... a certificate from a Microsft Certificate Server ... the SQL Service. ... SQL Server could not spawn FRunCM thread. ...
    (microsoft.public.sqlserver.security)