SQL Error – The EXECUTE permission was denied on the object…….
SQL Server has a number of fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access to the tables in the database. There is no role to grant a user permission to execute stored procedures, but this is easily resolved by creating a new role within SQL Server.
The following will work on SQL Server 2005 and above.
1. Create the following SQL query on the database that is giving the error ( this is usually displayed within the error message )
— Create a db_executor role
CREATE ROLE db_executor
— Grant execute rights to the new role
GRANT EXECUTE TO db_executor
2. Select “Security” and then “Users” on the database. Locate the user, rick click and select “Properties”
3. Select the “Membership” tab.
4. You will now see the “db_executor” box you can tick this and this should resolve the issue.