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
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;