2

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
Med Amin
  • 23
  • 1
  • 4
  • 2
    Have you considered, instead, putting the functions on a specific schema and then granting the roles/users execute on that schema? That would likely scale far better. – Thom A Nov 11 '20 at 09:27
  • @Lamu I don't get your point, but I edit the query with schemas as I understand it, it returns the list of querys – Med Amin Nov 11 '20 at 10:29
  • There is no `m` in my alias. – Thom A Nov 11 '20 at 10:33
  • I'm not sure how to really clarify that more, if I am honset: Have you (the OP) considered/thought about putting all the functions (the functions you have been creating) all on a specific schema (not `dbo` a different one for functions), and then granting the role (the one you are asking about here) the `EXEC` permission on that schema (the not `dbo` one, with the functions on). – Thom A Nov 11 '20 at 10:35
  • sorry I didn't undertsnd you really @Larnu – Med Amin Nov 11 '20 at 12:13
  • I am sorry for my mistake for your alias – Med Amin Nov 11 '20 at 12:13

0 Answers0