Is it possible to grant EXECUTE permissions on a custom function without compromising read-only access?

Question

We have an ancient database instance - Microsoft SQL Server 2008 R2 SP1 - that is running an (also obsolete) business-critical application. I know this is horrible, but for now it's not my call.

Long story short, I'm connecting a Metabase BI instance to it to harvest some key business data. Metabase attempts to use functions such as DateFromParts() that were introduced in later versions of SQL server and fail on ours. I found a re-implementation of DateFromParts() as a custom function here. However, the user Metabase is using to connect has read-only permissions (db_datareader) and isn't allowed to execute the custom function. I don't want to give it permissions willy-nilly; the reason I made it a read-only user in the first place was to ensure there was no possibility of it changing the data.

Is it possible to give it permission to execute the custom function while still preventing it from modifying the database? Are there any other security implications I should be aware of?

DISCLAMER: I have a fair bit of general programming experience, but only a little with databases, so please don't assume too much. :)

Answer 1

Is it possible to give it permission to execute the custom function while still preventing it from modifying the database? Are there any other security implications I should be aware of?

Functions in SQL Server may not change the database. Stored Procedures may. So you can safely grant EXECUTE on a function to a user without fear that they will change data. Even for a Stored Procedure if you know the stored procedure is read_only, you can grant EXECUTE on the procedure.

However, this won't help you. User-defined functions in SQL Server must be referenced with their schema. So when a user calls "DateFromParts" it won't be redirected to your custom function, which must be called as "dbo.DateFromParts".