0

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. :)

1 Answer 1

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".

2
  • Thank you! I did realize that I'd need to change the name in the auto-generate SQL query, but that's still easier than trying to recreate the logic with different functions. If there's a way around the schema thing, though, I'm all ears. :)
    – Matthew
    Jan 16, 2023 at 16:58
  • Nope. It's baked in. There's no way to replace a system function with a user-defined function. Jan 16, 2023 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.