CODEWITHSUNDEEP
sqlsql-serverentity-frameworkstored-procedures
AyeLetsGetCoding
AyeLetsGetCoding

Reputation: 47

EXECUTE permission denied on object

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.

Upvotes: 1

Views: 23338

Answers (2)

Amir
Amir

Reputation: 31

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

Upvotes: 2

Shahbaaz
Shahbaaz

Reputation: 421

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

Upvotes: 0

Related Questions