Clever Geek Handbook

Grant execute permission to SQL Azure

I recently migrated a database to SQL Azure. When I try to execute a stored procedure on this database on SQL Azure, I get 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!

+3


source to share


3 answers


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

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

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'
+10


source


You can always use this tool to manage your Azure SQL users and give them the correct permission.



+1


source


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

GRANT EXECUTE TO testuser
GO
+1


source






More articles:


All Articles