scalar function "select" permission issue when Grant has been applied "execute"

Question

scalar function "select" permission issue when Grant has been applied "execute"

graham.measures

Mr or Mrs. 500

Points: 535
More actions
January 10, 2024 at 11:52 am

#4359636

I have a scalar function used on a sql server 2016. I have created a database role using the user logging in.
The function works as it should returning a single value.
when migrated to another server running sql server 2022 the function fails with:-
Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'fnRule_JobNumber_Validation_multiple_jobcodes', database 'TSE_Automation', schema 'WDM_TSE'.
I have taken the contents of the function and can run with the login no problem. I have granted execute permission on the role and user. There is no DENY in the database. Any ideas?
Regards Graham
May 25, 2024 at 1:59 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 101124 More actions · Answer 1

There has to be a DENY somewhere to generate that message. DENY would override normal SELECT permissions.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 2

graham.measures wrote:

Msg 229, Level 14, State 5, Line 5 The SELECT permission was denied on the object 'fnRule_JobNumber_Validation_multiple_jobcodes', database 'TSE_Automation', schema 'WDM_TSE'.
I have taken the contents of the function and can run with the login no problem. I have granted execute permission on the role and user. There is no DENY in the database. Any ideas?
Regards Graham

You need to grant SELECT permissions not EXECUTE

ScottPletcher SSC Guru Points: 101124 More actions · Answer 3

Just to be sure, run this query in your db:

SELECT * FROM sys.database_permissions WHERE state = 'D'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

Thanks Scott I can confirm no Deny in database.

I can only grant execute not select permission to the scalar function. The error is stating select permission denied - as mentioned i can take the contents of the function and run it as a query without issue

Regards Graham

christophe.vanolmen SSC Enthusiast Points: 153 More actions · Answer 5

Poster has probably fixed his problem by now, but since I ran into the same issue today and didn't find a solution searching the internet, I'll share mine: the scalar function works again when setting database compatibility level to (in my case) 2017.

But that is not the solution you want, as SQL 2019 (and above) introduces scalar function inlining and if possible, you want this. So I disabled inlining for that specific function (CREATE OR ALTER yourfunction .... AS WITH INLINE = OFF) and that is the better solution.

I guess that the reason why this specific function failed in this way is that it references a linked server.