1

I'm currently working on an MVC5, EF6 project and needed a stored procedure for a piece of the project. I've written the stored procedure, and now when I try to use it within my code I get an error saying:

The EXECUTE permission was denied on object ....

Yet when I test the stored procedure in SQL Management Studio it let's me run the stored procedure just fine. I'm not really sure what to do to fix this as I've never come across this before.

AyeLetsGetCoding
  • 47
  • 1
  • 1
  • 5
  • 1
    is your sql user in the app the same one you are using in SSMS? – Jonesopolis Jun 02 '17 at 16:38
  • You should grant execute permission to the user – Numan KIZILIRMAK Jun 02 '17 at 16:43
  • 2
    check your web.config connection string to find the user you are connecting to the DB with then extend execute permissions to that user – Travis Acton Jun 02 '17 at 16:44
  • The account is my own. I have execute permission on another stored procedure that's used in code. Not sure why this one is so strange. – AyeLetsGetCoding Jun 02 '17 at 16:45
  • Are you looking at the same database? –  Jun 02 '17 at 17:04
  • 5
    There is a high likelihood that the process is running as a different user than in SSMS, and/or you're specifying credentials in your connection string that don't match the credentials in SSMS, and/or you're connecting to the wrong server/database. When stuff seems to defy logic, *challenge your assumptions*. –  Jun 02 '17 at 17:04
  • 1
    You really should create a separate account to access the SQL server from the WEB app. Never use your credentials! – Chuck Jun 02 '17 at 17:15
  • I believe the user you are using MVC app to hit the database does not have sufficient privilege to execute the stored procedure. you need to grant the execute permission to the user (or a group that they a part of) for the stored procedure. For e.g. USE AdventureWorks2012; GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo TO Recruiting11; GO – coolswastik Jun 03 '17 at 02:19
  • Yeah I figured this out, the way the database is set up, each stored procedure needs execute permission for the account linked to the project. I had never encountered that before which is why I had an issue. – AyeLetsGetCoding Jun 05 '17 at 14:37
  • Does this answer your question? [The EXECUTE permission was denied on the object 'xxxxxxx', database 'zzzzzzz', schema 'dbo'](https://stackoverflow.com/questions/3708348/the-execute-permission-was-denied-on-the-object-xxxxxxx-database-zzzzzzz-s) – Tomer Shetah Sep 27 '20 at 08:59

2 Answers2

1

First create an executor role and then grant exec permission to this role.Then make your user member of this role.

CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
EXEC sp_addrolemember 'db_executor', 'user1'

Hopefully this is enough but in case you still have issue check the below. The schema owner of SP and underlying objects should be the same for sql chaining permission to work. Check schema owners by:

select name, USER_NAME(s.principal_id) AS Schema_Owner from sys.schemas s

To change the owner of an schema you can:

ALTER AUTHORIZATION ON SCHEMA::YOUR_SCHEMA TO YOUR_USER;

Examples:

ALTER AUTHORIZATION ON SCHEMA::Claim TO dbo
ALTER AUTHORIZATION ON SCHEMA::datix TO user1;

Finally if within your SP you are truncating a table or changing structure you may want to add WITH EXECUTE AS OWNER in your SP:

ALTER procedure [myProcedure] 
WITH EXECUTE AS OWNER

as

truncate table etl.temp
Amir
  • 11
  • 1
0

Create a separate user role with access 'Execute' and then assign that to your current user. This is the best solution and helped me.

Please follow this link: https://stackoverflow.com/a/26871428/6761105

Shahbaaz
  • 403
  • 5
  • 11