Re: Granting xp_cmdshell permission to SQL Login

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@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F3731867-69A7-462A-8023-4DC8B7A1BE78@xxxxxxxxxxxxxxxx
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.

.

Relevant Pages