Granting Execute Permission on SQL Azure

Question

I recently migrated a database to SQL Azure. When I try to execute a stored procedure on this database on SQL Azure, I receive the following error:

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

My question is, how do I grant EXECUTE permissions to stored procedures (as well as read/write access to tables) on SQL Azure?

thank you!

Answer 1

Related to Joe Abrams' answer, you can grant execute permission to an individual user:

GRANT EXECUTE TO testuser
GO

Answer 2

The trick is that you have to create a custom "executor" role and then grant execute permissions to it.

In your master DB, first create a user if you don't have one already:

CREATE USER MyUser FOR LOGIN MyLogin WITH DEFAULT_SCHEMA=[dbo]
GO

Then, in your new DB:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GRANT EXECUTE TO [db_executor]
GO

sp_addrolemember @rolename = 'db_executor', @membername = 'MyUser'
sp_addrolemember @rolename = 'db_datareader', @membername = 'MyUser'
sp_addrolemember @rolename = 'db_datawriter', @membername = 'MyUser'