From: Uri Dimant on
George
Try
EXEC master..xp_cmdshell 'DIR C:\*.*'

Where does store CreateProcessAsUser stored procedure?



"George Wen" <windyqr(a)hotmail.com> wrote in message
news:OF0NJwoWJHA.4412(a)TK2MSFTNGP03.phx.gbl...
> Hi All,
>
> I was trying to assign non-sysadmin users permission to run cmdshell
> without luck.
>
> The steps I ran thru:
>
> 1. check sql server 2005 version
> --select @@version
> --shows SP2 3042
>
> 2, run cmdshell command as sysadmin user and it works
>
> --3, grant permission to xp_cmdshell
>
> USE master
> CREATE LOGIN JohnDoe WITH PASSWORD = 'jlkw#.6('
> CREATE USER JohnDoe FROM LOGIN JohnDoe
> GRANT EXECUTE ON xp_cmdshell TO JohnDoe
> go
>
> EXEC sp_xp_cmdshell_proxy_account 'mydomain\myacct', 'mypasswd'
> -- this is the sql service acct with local admin priviledge
> --and has sysadmin role in sql.
> go
>
> --Run as login x
> EXECUTE AS login = 'JohnDoe'
> EXEC xp_cmdshell 'DIR C:\*.*'
> REVERT
> GO
>
>
> the following error message turned up:
>
> An error occurred during the execution of xp_cmdshell. A call to
> 'CreateProcessAsUser' failed with error code: '1314'.
>
> I tried change the service account to network service and back to the
> service account via sql service configuration manager but that didn't help
> either.
>
> our sql server 2005 was created using windows 2003 clustering.
>
> Any help will be much appreciated.


From: Tibor Karaszi on
I'm pretty certain that the problem is that the service account lack
some privilege to create a process in some other user name context
(what we think of as "Run As").

The permissions needed are listed in below BOL URL. I'd work with an
OS person to verify that the service account do indeed have these
permissions (and that the aren't forced by some AD policy or similar).

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"George Wen" <windyqr(a)hotmail.com> wrote in message
news:OF0NJwoWJHA.4412(a)TK2MSFTNGP03.phx.gbl...
> Hi All,
>
> I was trying to assign non-sysadmin users permission to run cmdshell
> without luck.
>
> The steps I ran thru:
>
> 1. check sql server 2005 version
> --select @@version
> --shows SP2 3042
>
> 2, run cmdshell command as sysadmin user and it works
>
> --3, grant permission to xp_cmdshell
>
> USE master
> CREATE LOGIN JohnDoe WITH PASSWORD = 'jlkw#.6('
> CREATE USER JohnDoe FROM LOGIN JohnDoe
> GRANT EXECUTE ON xp_cmdshell TO JohnDoe
> go
>
> EXEC sp_xp_cmdshell_proxy_account 'mydomain\myacct', 'mypasswd'
> -- this is the sql service acct with local admin priviledge
> --and has sysadmin role in sql.
> go
>
> --Run as login x
> EXECUTE AS login = 'JohnDoe'
> EXEC xp_cmdshell 'DIR C:\*.*'
> REVERT
> GO
>
>
> the following error message turned up:
>
> An error occurred during the execution of xp_cmdshell. A call to
> 'CreateProcessAsUser' failed with error code: '1314'.
>
> I tried change the service account to network service and back to
> the service account via sql service configuration manager but that
> didn't help either.
>
> our sql server 2005 was created using windows 2003 clustering.
>
> Any help will be much appreciated.


From: Tibor Karaszi on
> Yes, there is a group policy that change security settings, after I
> changed the group policy it works straight away.

Thanks for reporting back, George. Always good to know whether a
suggestion work/helps or not.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"George Wen" <windyqr(a)hotmail.com> wrote in message
news:un5T7jyWJHA.1328(a)TK2MSFTNGP02.phx.gbl...
> Tibor Karaszi wrote:
>> I'm pretty certain that the problem is that the service account
>> lack some privilege to create a process in some other user name
>> context (what we think of as "Run As").
>>
>> The permissions needed are listed in below BOL URL. I'd work with
>> an OS person to verify that the service account do indeed have
>> these permissions (and that the aren't forced by some AD policy or
>> similar).
>>
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm
>>
>
> Thanks, Tibor.
>
> Yes, there is a group policy that change security settings, after I
> changed the group policy it works straight away.