Give a user execute permission to all Stored Procedures of a Single Database (MSSQL)

System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object …

This usually happens if the user does not have execute permission on the stored procedures  that the application is calling.

While there might be many different ways to handle this issue, the easiest way that i found is to :


1. First Select your Database

USE DATABASE ycsoftware

2. Create a database role for that database let’s say :

CREATE ROLE db_execute_stored_procedures 

3. Give execute permission to that role

GRANT EXECUTE TO db_execute_stored_procedures 

4.  Add the the user (the db user the application is running as) to the role.

EXEC sp_addrolemember ‘db_execute_stored_procedures’, ‘ycsoftware_user’


Reference :

http://msdn.microsoft.com/en-us/library/aa259605(v=SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa833199(VS.80).aspx