1

I have a user who is a member of the db_DataReader role (and no other roles apart from public), and has been granted explicit execute permission on a scalar function, but when they use the function

select hbp_plant.CatComments(42)

they get

The EXECUTE permission was denied on the object 'CatComments', database 'HBDevSIMCOA', schema 'HBP_Plant'*.

How do I give them permission to call the function without giving them any ability to modify the database?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Paul Davis
  • 23
  • 5

3 Answers3

2

Does the function access tables in different schemas, other than hbp_plant?

Instead of adding the db user to the db_datareader role, grant SELECT (for the whole db) and execute permissions on the function:

--db user = myreadonlyuser
grant select to myreadonlyuser; --can read from tables, table valued functions, views..in all schemas
grant execute on hbp_plant.CatComments to myreadonlyuser;
lptr
  • 1
  • 2
  • 6
  • 16
  • Thank you ... yes, in fact the function references tables from two other schemas ... see below for that bit of the function (character count prevents me posting the whole function). But grant select to HBRO; did not resolve the problem. declare C cursor for select f.Name, c.Comment from hbd_plant.HBComment c join hba_plant.HBV_Field f with (nolock) on c.FieldHBID = f.hbid where c.SQLID = @SQLID – Paul Davis Nov 23 '20 at 02:54
0

Give Exec Permission on tablename. Try this.

USE HBDevSIMCOA;
GRANT EXEC ON hbp_plant.CatComments TO PUBLIC

you can refer below link

The EXECUTE permission was denied on the object 'xxxxxxx', database 'zzzzzzz', schema 'dbo'

B.Muthamizhselvi
  • 642
  • 4
  • 13
  • Thank you, but this didn't work. As dbo ....GRANT EXEC ON hbp_plant.CatComments TO PUBLIC Commands completed successfully. Completion time: 2020-11-20T11:42:54.4893435+08:00 as HBRO select hbp_plant.CatComments(42) Msg 229, Level 14, State 5, Line 1 The EXECUTE permission was denied on the object 'CatComments', database 'HBDevSIMCOA', schema 'HBP_Plant'. Completion time: 2020-11-20T11:43:31.6359734+08:00 – Paul Davis Nov 20 '20 at 03:44
0

Try this:

CREATE SCHEMA [hbp_plant];

GO

CREATE FUNCTION [hbp_plant].[CatComments] (@A INT)
RETURNS INT
AS
BEGIN
    RETURN @A
END;

GO


CREATE USER [StackOverflow] WITHOUT LOGIN;

-- do not work
EXECUTE AS USER = 'StackOverflow';  
SELECT [hbp_plant].[CatComments](5) ;  
REVERT; 

GRANT EXECUTE ON [hbp_plant].[CatComments]  TO [StackOverflow];  


-- work
EXECUTE AS USER = 'StackOverflow';  
SELECT [hbp_plant].[CatComments](5) ;  
REVERT; 

DROP USER [StackOverflow];
DROP FUNCTION [hbp_plant].[CatComments]
DROP SCHEMA [hbp_plant];
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • Thank you, but this didn't work. As dbo ....GRANT EXEC ON hbp_plant.CatComments TO PUBLIC Commands completed successfully. Completion time: 2020-11-20T11:42:54.4893435+08:00 as HBRO select hbp_plant.CatComments(42) Msg 229, Level 14, State 5, Line 1 The EXECUTE permission was denied on the object 'CatComments', database 'HBDevSIMCOA', schema 'HBP_Plant'. Completion time: 2020-11-20T11:43:31.6359734+08:00 – Paul Davis Nov 20 '20 at 03:51