The EXECUTE permission was denied on the object, 'MyStoredProcedure', database 'MyDatabase', schema 'dbo'.

When you first create a login in SQL Server 2008, you can grant read, write, or ddl permissions by assigning the roles db_datareader, db_datawriter, and db_ddladmin, respectively. But by default, no specific role exists for giving the user execute permission. The easiest way to grant execute permission is by opening a SQLQuery window for that database, and running "grant execute to <user>", where user is the name of the user login.

For the command-line challenged, you can go to the database properties by right clicking the database name on the Object Explorer, select Properties to open the Database Properties dialog. On the left, select the Permissions page, under the Explicit tab, scroll down until you see the Execute permission, and check the Grant column like so.



This gives the user the ability to run any stored procedure in that database, including any stored procedure created afterwards.