How can I grant all users in my Azure SQL database the privilege to execute any function in that database?

Question

create role db_executor;
-- successful

grant execute to db_executor;
-- Msg 4613, Level 16, State 1, Line 1

EXEC sp_addrolemember N'db_execproc', N'my-user';
-- Msg 15151, Level 16, State 1, Line 1

Answer 1

@Jason Friedman Thank you for reaching out.

My understanding is that you are trying to grant all users permissions to execute any functions in your Azure SQL Database.You would want this:

create role role_ExecuteFunctionX;

grant execute

ON dbo.p1

to role_ExecuteFunctionX

You could use.

GRANT EXECUTE ON <function_name> to PUBLIC.

BUT

Public is builtin and everyone is always member of it. Just as in Windows AD there is the “Everyone” group. 

You could use public this way, but I would be careful and make sure to understand the system very well. You want to ensure that there won’t be surprises years down the road.

Excluding certain users could then be done using DENY.

The “clean” alternative would be to create a new “CanExecuteFunctionX”-role and make everyone member of it. Of course, you need proper processes in place to never forget a new user. But in an enterprise environment one would expect such approach. This way it’s explicit, not hidden (public is often forgotten about and no need for a deny in the exception case.)

Regards,

Oury

Answer 2

Hello @Jason Friedman

The sp_addrolemember stored procedure is not available in Azure SQL Database.
Try this SQL script

-- Create role 
CREATE ROLE db_executor; 
-- Grant EXECUTE privilege to the role 
GRANT EXECUTE TO db_executor; 
-- Add user to the role 
ALTER ROLE db_executor ADD MEMBER my_user;

Answer 3

The issue was I was executing the function in the wrong database.