Door Number 1, 2, or 3?...Security change, bug, or failed upgrade with an in-place upgrade from SQL Server 2016 to SQL Server 2019?
irl last edited by
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
ServerAwhere 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
ServerAand altered the function with the line
WITH SCHEMABINDINGcommented out, then
ServerBwas 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
ServerAwith only the
db_datareaderrole mapped to it in
ServerA(in addition to the
Publicdatabase role of course). There are no additional granular permissions set on it, and it is only assigned the
Publicserver role as well.
Interestingly enough, an alternative solution to my issue was to grant the
EXECUTEpermission either in
ServerAor specifically on
MyFunctionto the linked server account. BUT I did not have to grant the
EXECUTEpermission prior to the upgrade of
ServerAto SQL Server 2019, and my production servers (which very similarly mirror my development servers before this test upgrade) currently do not provision the
EXECUTEpermission 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
ServerBis getting a permissions error on the
EXECUTEpermission for only schema bound functions accessed across a linked server to
ServerA, after upgrading
ServerAfrom SQL Server 2016 to SQL Server 2019, simply put?
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.