Hi

I'm trying to execute a stored procedure which contains the below line.

exec master..xp_cmdshell 'echo abcd'

I granted permissions to the user by using the follwing lines

sp_grantdbaccess 'LimitedUser'
go

grant exec on xp_cmdshell to LimitedUser
go

but when I execute the stored procedure I'm getting the following error

"The xp_cmdshell proxy account information cannot be retrieved or is
invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists
and contains valid information."

What is that I'm missing? Please let me know.

Thanks in advance

chandra

As the message indicates, you need to configure the xp_cmdshell proxy
account on order for non-sysadmins to execute xp_cmdshell.  From Enterprise
Manager, expand the server node and select SQL Agent-->Properties-->Job
System.  Uncheck the 'Only users with SysAdmin privileges...' checkbox and
specify the desired Windows account to use as the OS security context.  The
account should have the minimal permissions needed for your functionality.

Note that this will allow LimitedUser to execute any ad-hoc OS command,
limited only by the proxy account OS permissions.  If your user proc is
owned by dbo and in the master database, this GRANT is not necessary since
the ownership chain is unbroken.

If your proc is in a user database, you might consider enabling the 'db
chaining' database option and changing the user database owner to 'sa'.
This will provide an unbroken ownership chain so that non-sysadmin users can
execute xp_cmdshell only via your proc.  However, be mindful that you should
enable 'db chaining' in an sa-owned database when only sysadmin role members
can create dbo-owned objects in that database.  See Cross DB Ownership
Chaining in the Books Online for more information.

See

Show quoteHide quote

Hi

I'm using SQL Server 2005

Thanks
Chandra

Show quoteHide quote

If you haven't already done so, you need to enable xp_cmdshell:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Specify the account using sp_xp_cmdshell_proxy_account:

EXEC sp_xp_cmdshell_proxy_account 'MyDomain\MyAccount','MyPassword'
GO

You also have more secure options in SQL 2005 (e.g. EXECUTE AS) so you don't
need to grant direct execute permissions or enable 'db chaining'.

Show quoteHide quote

Best way to insert data into tables without primary keys
What is new in Sql Server 2005 cursors?
error converting datatype varchar to numeric
Update Query in SQL 2005 with inner join
nesting select statements