The following article will handle “the execute permission was denied on the object” error appears on SQL Server. This error is clear and is related with the permissions of the user that is executing or running that object on database. To resolve this most probably you need to contact with your database administrator.
The error view example:
“The EXECUTE permission was denied on the object ‘XXXXX’, database ‘master’, schema ‘dbo’ “
The EXECUTE Permission was Denied on the Object
Solutions for “The execute permission was denied on the object”
The reason of the error:
User has not permission to run “XXXXX” stored procedure in master database. If you are database admin then you need to give permission to the user performing the execution like below.
As user you can still try but it is not likely the same user that cannot execute the procedure also has the rights to change the permissions. If this does not work, you need to login on SQL Server as a user that has the rights to change the execute permissions. Or better contact a SQL Server Database Admin to resolve this for you.
1. How to resolve the error by Grant permission to specific object:
After you are login with an admin user or user that have rights to change execute permissions, you can grant permissions to the user executing this procedure like below:
use [master]
GO
GRANT EXECUTE ON [dbo].[XXXXX] TO [UserYY]
You can customize the above query by changing the procedure [dbo].[XXXXX] or [UserYY] as you need.
2. Resolve issue by grant permission to db_owner role:
A solution is to add db_owner role to the user on the database where is trying to execute. You can add this easy by:
- Open SQL Server Management Studio and go to properties of User
- Click to User Mapping
- Check the Database where you are going to give the db_owner role
- Below this on “database role membership” section check db_owner role
- Click ok And try to execute the procedure.
3. Resolve by create a new database role:
Curiously there is no role to grant a user permission to execute stored procedures. The database administrators recommend to create a new database role like db_executor.
CREATE ROLE db_executor;
–Grant that role exec permission.
GRANT EXECUTE TO db_executor;
To add this new permission to user you need to:
- Go to the properties of the user
- Go to User Mapping
- Select the database where you have added new role
- New role will be visible in the Database role membership
- Check db_executor role and save
4. Resolve by giving everybody execution permission:
This extreme solutions in my opinion because is going to give to every user the execution permission. This can be happening by executing below query:
GRANT Execute on [dbo].your_object to [public]
“Public” is the default database role that all users are a member of.
Read Also: Reset SQL SA Password
5. Resolve by grant sysadmin server role to the user:
- Open SQL Server Management Studio and go to properties of User
- On Server Roles section check sysadmin and click ok
- Check if you can execute the procedure
Be careful because sysadmin server role grand all the permission for the all databases on that instance.
grant sysadmin server role
Conclusions:
So in the above article we added all the solutions to solve The EXECUTE Permission was Denied on the Object. If you find any other solutions just reply on comment. We will try to include it on our article.
