You want to create a new user with read and write access who also has ability to execute all user defined functions but you don't want to make the user a db_owner. The code below will do a GRANT EXECUTE/SELECT for all the user defined functions in the DB If the user defined function is a table-valued function then you need to grant select permissions otherwise you need to grant execute permissions
Right now this code will print the GRANT EXECUTE/SELECT statements, change the PRINT to EXEC if you want it to be done automatically
--Grab all the functions for the current DB
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME,DATA_TYPE
INTO #FunctionList
FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0
AND ROUTINE_TYPE='FUNCTION'
ORDER BY SPECIFIC_NAME
DECLARE
@Loopid INT,
@MaxId INT,
@UserName VARCHAR(50)
--This is the user that will get the execute/select permissions
SELECT @UserName = 'SomeUser'
--Grab start and end values for the loop
SELECT @Loopid = 1,
@MaxId = MAX(ID)
FROM #FunctionList
DECLARE
@SQL VARCHAR(500),
@ProcName VARCHAR(400) ,
@Permission VARCHAR(20),
@DataType VARCHAR(20)
--This is where the loop starts
WHILE @Loopid <= @MaxId BEGIN
--grab the function name and type
SELECT @ProcName = SPECIFIC_NAME, @DataType =DATA_TYPE
FROM #FunctionList
WHERE ID = @Loopid
--Find out if it's a table-valued function
IF @DataType ='TABLE'
SELECT @Permission ='SELECT'
ELSE
SELECT @Permission ='EXECUTE'
--construct the statement
SELECT @SQL = 'GRANT ' + @Permission +' ON [' + @ProcName + '] TO ' + @UserName
PRINT (@SQL) --change PRINT to EXECUTE if you want it to run automatically
--increment counter
SET @Loopid = @Loopid + 1
END
--clean up
DROP TABLE #FunctionList
| 
