SQL Error – The EXECUTE permission was denied on the object

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.