sqlsql-server-2016sql-function

How to Grant Execute All Functions In SQL Server to group


I have a list of function I need to grant execute to specific group, I found that the query should be executed like this

GRANT EXECUTE ON [dbo].[FunctionName] TO [GroupName]

But since I have 40 functions in the database, so I need to execute it 40 times with updateing the finction name. And when I haave new unctions I need to execute the new one the same way, my question if there is a Script/Function that get all the functions name in the database and execute all just once. I am able to get the list of functions with this code:

SELECT name AS function_name
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'

and also I create a function to execute it:

ALTER function [dbo].[listFunction]()
returns int
AS
  BEGIN
  declare @Result int;
  declare @ListFn nvarchar(100);
  set @ListFn = (
                        SELECT name AS function_name
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'
                        );
if @ListFn=null
set @Result = 1
else
set @Result = 0
return @Result
end

but what is missing how to iterate each function and grant execute, so any help?

Edit: I have edited the query with this using the chema

DECLARE @SchemaName varchar(max)
DECLARE @GroupName varchar(max)
SET @SchemaName = 'dbo'
SET @GroupName = '[groupname]'
select 'GRANT EXECUTE ON OBJECT::' + @SchemaName + '.' + P.name  + ' to ' + @GroupName
from sys.objects P
inner join sys.schemas S on P.schema_id = S.schema_id
where S.name = @SchemaName
and P.type_desc LIKE '%FUNCTION%'

Edit 2: I craete a procedure to execute the grant, because it cnnot work with function, so I executed it and no execeptionsshown, but I don't knwo if it i right or not:

CREATE PROCEDURE ExecuteAllFunctions
AS
  BEGIN
  DECLARE @i int
  declare @Result int;
  declare @ListFn nvarchar(100);
  DECLARE @a TABLE (fnct nvarchar(max))
  DECLARE @SchemaName varchar(max)
  --DECLARE @a TABLE (id uniqueidentifier)
DECLARE @GroupName varchar(max)
SET @SchemaName = 'dbo'
SET @GroupName = '[GroupName]'

INSERT INTO @a select 'GRANT EXECUTE ON OBJECT::' + @SchemaName + '.' + P.name  + ' to ' + @GroupName
from sys.objects P
inner join sys.schemas S on P.schema_id = S.schema_id
where S.name = @SchemaName
and P.type_desc LIKE '%FUNCTION%'

--EXEC @ListFn
--SET @i = 1
--WHILE (@i <= (SELECT MAX(fnct) FROM @a))
--    BEGIN
--  set @ListFn = (select fnct from @a a where a.fnct=)
        
--        SET @i = @i + 1
--    END

--if @ListFn=null
--set @Result = 1
--else
--set @Result = 0
--return @Result
end

Solution

  • Perhaps use a cursor for your loop:

    In my table variable version, I just selected the name. Then:

    USE The_DB_Name;
    DECLARE @start_cmd varchar(100)='GRANT EXECUTE ON OBJECT::[dbo].[';
    DECLARE @end_cmd varchar(100)='] TO The_Group_Or_User_For_Application';
    DECLARE @fcmd varchar(300);
    DECLARE @fname varchar(50);
    
    INSERT INTO @a 
        SELECT name AS fn_name
        FROM sys.objects
            WHERE type_desc LIKE '%FUNCTION%';
    
    DECLARE my_cursor CURSOR FOR SELECT fnct FROM @a;
    OPEN my_cursor;
    FETCH NEXT FROM my_cursor INTO @fname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @fcmd = CONCAT(@start_cmd,@fname,@end_cmd) ;
        SELECT @fcmd AS Grant_Call; -- just to see what is happening
        EXEC(@fcmd);
        FETCH NEXT FROM my_cursor INTO @fname;
    END
    CLOSE my_cursor;
    DEALLOCATE my_cursor;