[SOLVED] permission was denied on the object xp

permission was denied on the object xp_cmdshell

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.

Solution

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.