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.
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
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
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:
- Did something security related change from SQL Server 2016 to SQL Server 2019 that I did not realize or...
- Does this sound like a bug I've encountered or...
- Do you think I botched my in-place upgrade on
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?