permission was denied on the object xp_cmdshell

Question

I am getting Execute permission was denied on the object 'xp_cmdshell'.

Here's the situation, I have a stored procedure called ExportFile. I am calling the stored procedure via SqlCommand from a web application from a Virtual PC.. during the execution of this command i get permission error

Then I debug it via SQL profiler and execute the result from the profiler to a query window (this means i run the StoredProcedure with the necessary parameters basing from the profiler to a Query window) and surprisingly its working just fine. the file was exported successfully.

I wonder whats wrong with this considering that my login in the connection string is an owner and admin user.

score 3 · Accepted Answer · answered Dec 14 '11 at 21:20

3

The SQL user account being used with the web server doesn't have permissions to use that extended procedure. You would have to elevate the rights of that user (bad idea) or assign a proxy account within SQL security that can execute the procedure within the server without making the web account a Sysadmin.

answered Dec 14 '11 at 21:20

Phil

46
1

Thanks! I solved it. is this what you mean? EXEC sp_xp_cmdshell_proxy_account [domain\user], [password]; EXEC sp_grantdbaccess [sql account user] GRANT exec ON sys.xp_cmdshell TO [sql account user] – Sharles Phirans Dec 14 '11 at 21:57

score 1 · Answer 2 · edited Feb 24 '14 at 08:26

1

We have to give this user execute permission

Syntax:

GRANT EXECUTE ON xp_cmdshell TO [Domain\User]

Note : Make sure you must be using login on which you give execute permission. I found many times user do use diff id and complain.

edited Feb 24 '14 at 08:26

Flexo

87,323
22
191
272

answered Feb 04 '14 at 20:23

Saurabh Sinha

1,363
1
9
11

score 0 · Answer 3 · answered Aug 12 '19 at 08:08

Assuming you get a message like

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Then you first need a proxy user which SQL Server uses to access the host operating system.

Setup the proxy user

execute sp_xp_cmdshell_proxy_account 'DOMAIN\USER', 'PASSWORD'

In SQL Server Management Studio this should look like the following:

Ensure that the SQL user (not the proxy user) is mapped to the master database

use [master]
go

execute sp_addrolemember 'public', SQLUSERNAME;
execute sp_addrolemember 'db_datareader', SQLUSERNAME;
execute sp_addrolemember 'db_datawriter', SQLUSERNAME;

In SQL Server Management Studio, this should look like this:

Grant the execute permission, such that the SQL user can use the proxy to access OS functionality

use [master]
go

grant execute on xp_cmdshell to SQLUSERNAME

permission was denied on the object xp_cmdshell

3 Answers3

Linked