How to grant access to all functions to a user in one shot (but two steps :P)

Have you ever tried to allow a user of execution of all functions in a database? Imagine such a case – you have a database and a service user which is supposed to do all DML but no DDL. You can’t assign this user to db_owner (SOX forbid!), and a combination of db_datareader and db_datawriter fixed database roles is not sufficient, because it does not allow stored procedures and functions to be executed.

Mass grant of EXECUTE permissions to stored procedures might be tricky since a stored procedure can contain DDL, plus ownership chaining is effective. In case of stored procedures you should carefully select which users are able to run procedures. In case of functions it’s simple – they allow only for DML, so it’s valid for a service user to be able to execute all functions in a database. The only culprit is that for EXECUTE permission is required for scalar function, while for table-valued function SELECT permission applies. 

The following code produces the script that you can execute to automatically assign proper permissions to functions:

with users as 
(select 'user1' as name
union all
select 'user2'
union all
...
select 'userN')

select 'grant execute on ' + ss.name + '.' + so.name + ' to ' + users.name as script from sys.objects so 
inner join sys.schemas ss on so.schema_id = ss.schema_id
cross join users
where so.type_desc = 'SQL_SCALAR_FUNCTION'
union all
select 'grant select on ' + ss.name + '.' + so.name + ' to ' + users.name as script from sys.objects so 
inner join sys.schemas ss on so.schema_id = ss.schema_id
cross join users 
where so.type_desc = 'SQL_INLINE_TABLE_VALUED_FUNCTION'

Simply add all users for whom you want to grant access in users CTE, execute, and then copy the results to script window and run.

About these ads