Granting xp_cmdshell permission to SQL Login

Hi database geeks and MVPs!
Using SQL Server 2005 SP2
I have a stored procedure in my database which calls xp_cmdshell to run a
little task. I have done the following to allow this proc to be executed by
a
non-privileged user:
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
I get the following error:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
through via a stored procedure?
Thanks,
Mark.Mark
Did you restart MSSQLSERVICE after granting permissions?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Yes I did. No effect.
"Uri Dimant" wrote:

> Mark
> Did you restart MSSQLSERVICE after granting permissions?
>|||Hello Mark,
You can't use xp_cmdshell because you do not enable it. Because of the
'allow updates' option.
Your 'allow updates' server setting must be enabled. If you want to leave it
enabled then you'll need to run your code as the following to enable
xp_cmdshell
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
If you disable your 'allow updates' option using the following code
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
Then you'll be able to run your code as it is (without with override
thing...)
P.S.
I learned this solution from Jasper Smith, thanks to him.
Ekrem ?nsoy
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Mark
xp_cmdshell requires CONTROL SERVER permission.
Does the user have it?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
> Yes I did. No effect.
> "Uri Dimant" wrote:
>
>|||Hi Uri,
I have changed my script to incorporate that permission, but I still get the
same error. xp_cmdshell works fine when run as a sysadmin.
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
--EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
doesn't work either
GRANT CONTROL SERVER TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
go
/*
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
*/
"Uri Dimant" wrote:

> Mark
> xp_cmdshell requires CONTROL SERVER permission.
> Does the user have it?
>
>
>
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
>
>|||When I type "NET HELPMSG 1329" from the command prompt, I get message "Logon
failure: user not allowed to log on to this computer."
Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
has permissions to login locally.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:FF048394-0029-4E1B-931F-E6B9E2F9CFF3@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I have changed my script to incorporate that permission, but I still get
> the
> same error. xp_cmdshell works fine when run as a sysadmin.
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> --EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
> doesn't work either
> GRANT CONTROL SERVER TO mysqllogin
>
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> go
> /*
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> */
>
> "Uri Dimant" wrote:
>|||Hi Dan,
That's the point. I cannot grant access to a SQL Login. It works fine with a
Windows login, but I have a SQL Login.
From BOL:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password'
} ]
Arguments
NULL
Specifies that the proxy credential should be deleted.
account_name
Specifies a Windows login that will be the proxy.
Mark.
"Dan Guzman" wrote:

> When I type "NET HELPMSG 1329" from the command prompt, I get message "Log
on
> failure: user not allowed to log on to this computer."
> Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
> has permissions to login locally.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
xp_cmdshell needs an OS security context when it runs. That security
context is the Windows xp_cmdshell proxy account when it's executed by a
non-sysadmin user. The 1329 error isn't related to the SQL login executing
xp_cmdshell but is rather the Windows error code returned because the
xp_cmdshell proxy account doesn't have the needed Windows permissions
My guess is that the Windows login you mentioned is a sysadmin role member.
Xp_cmdshell runs under the context of the SQL Server service account when
executed by a sysadmin role member and that account probably has different
permissions than the proxy account
I successfully ran a modified of your original script on my test system. My
xp_cmdshell proxy account is a minimally privileged domain user account and
. I didn't grant CONTROL SERVER permission..
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:61EA5CCA-4C65-410C-959B-7B67195F2DFD@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
> From BOL:
> sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'passwor
d' } ]
> Arguments
> NULL
> Specifies that the proxy credential should be deleted.
> account_name
> Specifies a Windows login that will be the proxy.
>
> Mark.
> "Dan Guzman" wrote:
>