xp_cmdshell

[ngyoun]

SQL 7.0
I have given a non sa user permissions to run xp_cmdshell via his NT logon. When he runs it and does a "dir" of the server it works. When he trys to do a "dir" of his own machine it comes up with "Logon failure unknown user name or bad password".
SQL Agent is running under a domain admin account. I have placed SQLAgentCmdExec in the servers local administrators group. I have de-selected the option from SQLAgent properties\Job System which restricts non sa users executing CmdExec stuff. I did attempt to Reset Proxy Account and Reset Proxy Password. When I clicked on these it just gave me message that the account and password had successfully been reset without asking me for username, password or domain.

[skhanal]

You need to grant permission to the account in your machine. If it is a share then grant at least read permission. If it is not a share then you need to include the account in local admin group of your computer.

[rmiao]

Since the use is not member of sql sysadmin, system runs xp_cmdshell under sql server's local account sqlagentcmdexec's credential which doesn't have permission on remote machine at all.

[ngyoun]

Yes, I have tried that and it still gives the error. How can I find out what account the SQLAgentCmdExec is running under? Is it the same account as SQLAgent? If so I don't understand why it isn't working because it is a domain admin account.

[MAK]

start - run - services.msc and scroll down to sqlserveragent and see the logon credential.

[rmiao]

SQLAgentCmdExec is local account on sql server, not service. Which account the xp_cmdshell runs under depends on who calls xp_cmdshell. If called by member of sysadmin, it runs under sql service account. If called by non-sysadmin, it runs under SQLAgentCmdExec. Since SQLAgentCmdExec is local account, it has no permission on remote machine. That means non-sysadmin user can't access remote machine with xp_cmdshell.