SELECT PERMISSION DENIED ON Scalar function

Question

SELECT PERMISSION DENIED ON Scalar function

graham.measures

Mr or Mrs. 500

Points: 535
More actions
July 9, 2024 at 11:42 am

#4434489
I have given examples of two ways of getting results from scalar function - one works the other doesn't
--GRANT EXECUTE ON WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes TO [<domain>\<user>]
DECLARE @result INT;
SET @result = WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes(N'');
SELECT @result;
--GRANT SELECT ON WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes TO [<domain>\<user>]
SELECT WDM_TSE.fnRule_JobNumber_Validation_multiple_jobcodes(N'');
For a user with permissions, the first works, the second doesn't work including the grant. I have sysadmin both work for me.
But why isn't there a select permission for scalar functions
error returned for user: "Msg 229, Level 14, State 5, Line 32
The SELECT permission was denied on the object 'fnRule_JobNumber_Validation_multiple_jobcodes', database 'TSE_Automation', schema 'WDM_TSE'. " NB: The function parameter is not blank in our system
- This topic was modified 4 months, 2 weeks ago by graham.measures.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

The key difference is a result set. Scalar functions return a value, not a result set. So they're not being "selected" from, they're being executed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

graham.measures Mr or Mrs. 500 Points: 535 More actions · Answer 2

Thanks Grant

I accept your point - why is the system giving me a "select permission denied" when using the scalar variable in a select statement, when the user is not sysadmin. When the user is sysadmin the statement runs correctly.

Kind Regards

Graham

graham.measures Mr or Mrs. 500 Points: 535 More actions · Answer 3

graham.measures

Mr or Mrs. 500

Points: 535

July 9, 2024 at 1:34 pm

#4434520

Maybe the function name is too long?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

graham.measures wrote:

Thanks Grant
I accept your point - why is the system giving me a "select permission denied" when using the scalar variable in a select statement, when the user is not sysadmin. When the user is sysadmin the statement runs correctly.
Kind Regards
Graham

Hmmm... Good question. Testing this, granting EXEC on a function just worked in the SELECT, just fine.

CREATE FUNCTION test
    ()
RETURNS INT
--WITH ENCRYPTION|SCHEMABINDING, ...
AS
BEGIN
DECLARE @mycount int;
SELECT @mycount = COUNT(*) FROM dbo.Addresses AS a;
    RETURN @mycount;


END
GO

GRANT EXECUTE ON dbo.test TO bob;

bob has no other permissions in the database. He can't even query the dbo.Addresses table. Function worked. I'm not seeing what's wrong in the code you posted, but I'm assuming something else is up.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning