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

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: Compromise?
    ... Yes, if you don't provide a password on your SA account, anybody able to run ... and connect now has complete control over your SQL Server. ... Server has. ...
    (microsoft.public.sqlserver.security)
  • Re: Windows Auth to SQL Server from ATL Web Service not working...
    ... account I'm logged on as. ... SQL on a different box from my web service in an Atl Server web ... impersonation token is not passed on to the SQL Server. ... Event Category: Account Logon ...
    (microsoft.public.vc.atl)
  • Re: Discussing 3 different strategies for deleting from multiple tables
    ... I will be using SQL Server but I am riding on top of a third party ... FYI, Account contains around 20K ... >>> This results in one parameterized query followed by two more trips to ...
    (microsoft.public.data.ado)
  • RE: connection problems in secondary site and SQL server
    ... Do you have a Windows 2003 server anywhere in your environment? ... i can't add this account to this group. ... SMS Management Point encountered an error when connecting to its Database ... SMS on SQL Server My_Primary_SMS_Server. ...
    (microsoft.public.sms.admin)
  • RE: MP Install issue
    ... Will setting the SPN on the domain account fix the communication issue ... >> MPDB ERROR - CONNECTION PARAMETERS ... >> with a trusted SQL Server connection. ...
    (microsoft.public.sms.setup)