Friday, 27 May 2016

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.    1.  Select “Security” and then “Users” on the database.  Locate the user, rick click and select “Properties”
3.    2.  Select the “Membership” tab. 
4.    3.  You will now see the “db_executor” box you can tick this and this should resolve the issue.