Grant Alter to Only to All Functions on SQL Server

Question

Need help to GRANT ALTER only to all the SQL Server Functions.

Below is mycode:

    GRANT ALTER ON FUNCTION::dbo TO [username]

Answer 1

According to the MS documentation, ALTER FUNCTION requires ALTER permission on the function itself or the schema. You can't grant ALTER on all functions without allowing ALTER on other objects.

You should set up a role with the required permissions and then add users to that role.

CREATE ROLE edit_functions AUTHORIZATION [db_owner];

ALTER ROLE edit_functions ADD MEMBER [username];

GRANT ALTER ON [schema].[function1] TO edit_functions;
GRANT ALTER ON [schema].[function2] TO edit_functions;

etc.

If you edit the role properties in SSMS, the "securables" page allows you to search for "all objects of the types..." and you can pick the types for functions.