SQL Azure currently has fairly limited management capabilities. When you create a database, you receive an administrator account that is tied to your login (you can change the SQL Azure password, though). Because there is no GUI for user management, there’s a temptation to use this account in all your applications, but I highly recommend you create users for your application that have limited access.
If you limit access to only stored procedures, you need to specify execute permissions. Assuming you want your connection to have execute permissions on all stored procedures, I recommend a new role that has execute permissions. That way, you can simply add users to this role and as you add more stored procedures, it simply works. To create this role, you can do something like this:
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
Now in the master database (currently, you need to do this in a separate connection – just saying ‘use master’ won’t work) you can create your login for the database:
CREATE LOGIN MyUserName
WITH PASSWORD = 'Password';
In your application database, you need to create a user – in this case, we’ll just create a user with the same name as the login:
CREATE USER MyUserName FOR LOGIN MyUserName;
Next, we’ll specify the appropriate roles. Depending on your needs, you may need only datareader. I recommend db_owner only if necessary.
-- read/write/execute permissions
EXEC sp_addrolemember N'db_datareader', N'MyUserName'
EXEC sp_addrolemember N'db_datawriter', N'MyUserName'
EXEC sp_addrolemember N'db_executor', N'MyUserName'
-- only if you need dbo access:
EXEC sp_addrolemember N'db_owner', N'MyUserName'
You can continue to customize as necessary, as long as you are familiar with the appropriate T-SQL.