5

What I Did:

Recently I tested doing an in-place upgrade on ServerA, a development server that was running SQL Server 2016 (SP 2 CU17) Standard Edition to SQL Server 2019 Standard Edition. (I know this is not the preferred way to do an upgrade, but again just a test on a development server, so no harm no foul.)

During the Installation Wizard, one of the steps was hanging for a long time, so my co-worker clicked the Next button to skip the step. I can't quite recall which step it was, but I believe it was either the Product Updates or Install Setup Files step. I recall it next said it had skipped a few different types of downloads. The rest of the installation went ok, and it finished successfully.

I was able to start up the instance, login, and access our database. I then increased the database Compatibility Level to 150 (SQL Server 2019's Compatibility Level). I ran some queries for performance testing, and then ultimately decided to turn on the Legacy Cardinality Estimator. Everything appeared to be working so far.

What Had Happened Was:

Then I noticed something interesting on ServerB, another development server that was already running SQL Server 2019, and has a linked server setup pointing to ServerA. Everything was running fine on ServerB, except any queries that referenced a view across the linked server to ServerA where that view used a schema bound scalar function inside of it. I was receiving error The EXECUTE permission was denied on the object 'MyFunction', database 'Database1OnServerA', schema 'dbo'. If I went back to ServerA and altered the function with the line WITH SCHEMABINDING commented out, then ServerB was able to select from the view that references that function again.

Additional Information:

The account used in the linked server object is a SQL Server Login on ServerA with only the db_datareader role mapped to it in Database1 on ServerA (in addition to the Public database role of course). There are no additional granular permissions set on it, and it is only assigned the Public server role as well.

Interestingly enough, an alternative solution to my issue was to grant the EXECUTE permission either in Database1 on ServerA or specifically on MyFunction to the linked server account. BUT I did not have to grant the EXECUTE permission prior to the upgrade of ServerA to SQL Server 2019, and my production servers (which very similarly mirror my development servers before this test upgrade) currently do not provision the EXECUTE permission to the linked server account either.

Door Number 1, 2, or 3:

  1. Did something security related change from SQL Server 2016 to SQL Server 2019 that I did not realize or...
  2. Does this sound like a bug I've encountered or...
  3. Do you think I botched my in-place upgrade on ServerA?

Any other ideas on why ServerB is getting a permissions error on the EXECUTE permission for only schema bound functions accessed across a linked server to ServerA, after upgrading ServerA from SQL Server 2016 to SQL Server 2019, simply put?

1 Answer 1

9

Door Number 1, 2, or 3:

2, with a dash of 3.

An ownership chain should prevent permission checks on a scalar UDF referenced from a view, so a user with SELECT permission on the view should not also need EXECUTE permission the UDF owned by the view's owner.

Looks like this one of the many TSQL scalar UDF inlining bugs that were present in RTM and fixed in some CU. It reproed for me on RTM and stopped after upgrading to CU14.

SQL Server doesn't usually slipstream Cumulative Updates (or Service Packs*), so the installer installs RTM, and then you patch after upgrade. Usually an updated installer will be built for GDR updates, but there's no reason to stay on RTM+GDR after a major version upgrade.

It also stopped doing that on RTM when I set

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

*SQL Server 2017 and later don't have service packs, only CU's.

4
  • Sweet, exciting I found my first bug (even if it's not relevant anymore lol)! 🙂 So if I understand correctly, ServerA is currently on RTM (from the installer) and if I patch it to the latest CU (14) then this but should be resolved?
    – J.D.
    Jan 15, 2022 at 16:52
  • Yes. I believe so. Jan 15, 2022 at 16:52
  • Sweet, thanks David, appreciate the input as always! Going to give this a go when I'm back in the office on Monday.
    – J.D.
    Jan 15, 2022 at 16:53
  • FWIW, patching to the latest CU (14) seemed to fix the issue so far. Thanks again!
    – J.D.
    Jan 20, 2022 at 2:12

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.