SQL Server 2016 running in a Azure VM
Production database has a stored proc that executes as owner and works fine executing xp_regread to get the tcp/ip domain name.
A test copy of that database created by backup and restore, on same server, reports Access Denied on the same store procedure.
I deleted the test database and restored a fresh copy and still no joy.
If I remove the WITH EXECUTE AS OWNER it works on both databases.
Fully deployed needs to run WITH EXECUTE AS OWNER as it will get called by client applications running minimal service account permissions.
There are 20 other identically schema'd databases it works fine on.
I suspect something in the master data permissions has gotten askew, related specifically to the tst database
I have tried deny execute on xp_regread to public and **grant execute on xp_regread to public, neither way makes a difference
Any help would be appreciated.
CREATE procedure spRegreadTest WITH EXECUTE AS OWNER AS BEGIN DECLARE @Domain varchar(64) EXEC master..xp_regread `HKEY_LOCAL_MACHINE`, `SYSTEM\CurrentControlSet\services\Tcpip\Parameters`, N`Domain`,@Domain OUTPUT select Current_user AS [CurrentUser],original_login() AS [OriginalLogin],@Domain as [Domain] END
exec spRegreadTest CurrentUser OriginalLogin Domain dbo XXXSupport azure.XXXXXX.net (1 row affected)
exec spRegreadTest CurrentUser OriginalLogin Domain dbo XXXSupport NULL Msg 22001, Level 16, State 1, Line 0 xp_regread() returned error 5, `Access is denied.` (1 row affected) exec sp_stored_procedures spRegreadTest PROCEDURE_QUALIFIER PROCEDURE_OWNER PROCEDURE_NAME NUM_INPUT_PARAMS NUM_OUTPUT_PARAMS NUM_RESULT_SETS REMARKS PROCEDURE_TYPE ESP_OR_CSD_tst dbo spRegreadTest;1 -1 -1 -1 NULL 2