question

ScottCropley-6671 avatar image
0 Votes"
ScottCropley-6671 asked ErlandSommarskog commented

xp_regread() 'Access is denied.' on test database others databases works fine.

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



Procedure

 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

Prod Database

     exec spRegreadTest
        
     CurrentUser    OriginalLogin    Domain
     dbo            XXXSupport    azure.XXXXXX.net
        
     (1 row affected)

Test Database

     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














sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Goo day,

A test copy of that database created by backup and restore, on same server, reports Access Denied on the same store procedure.

My first guess is that this related to orphaned users. This is comment when restore to different server but maybe you are using older backup which has the same issue...

The restored database might have a different login than the original database even if they have the same name.

(1) Check the SID of the owner of the databases (original and the restored one) and confirm that it is the same.

(2) If this an old backup then try to create a new backup and restore it (I assume that you backup and restore from/to the same server as you said, if not then this is even more common).

on same server,

This confuse me a bit as usually this common when restoring to a different instance where you have different logins... but let's confirm it as I wrote above


If I remove the WITH EXECUTE AS OWNER it works on both databases.

This fit the above reason. Once you do not execute as owner then the wrong owner is not used.


 SELECT dp.type_desc, dp.sid, dp.name AS user_name  
 FROM sys.database_principals AS dp  
 LEFT JOIN sys.server_principals AS sp  
     ON dp.sid = sp.sid  
    
 SELECT [name], [sid] 
 FROM sys.sql_logins 

return with the result and we will continue from here if this is not the issue





0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

Since you say it works with EXECUTE AS OWNER, I assume that you have set the database as TRUSTWORTHY. This flag is cleared when you restore the database.

No, don't go and set it again. That flag can be a security risk. If there are users in the database who have elevated permissions so that they can create and impersonate users, they can use that to elevate their permissions to be sysadmin.

Instead the secure way to do this is to use certificate signing. That is you create a certificate which must be both in master and the user database, and you sign the procedure with the certificate. Then you create a login from the certificate which you grant the required permissions. (Which I suspect is membership in sysadmin in this case.)

I have written about this technique in a lot more detail in an article on my web site: https://www.sommarskog.se/grantperm.html. In this article, I also discuss in more detail why TRUSTWORTHY is dangerous.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Face Palm! TRUSTWORTHY was the problem!
I normally do database copies with a stored proc that sets the proper database settings, but had recently been playing with memory based tables, which requires FILESTREAM to be setup, and our copy proc did not know about the the FILESTREAM file, so was failing, causing me to do the restore part of the copy manually, and it did not properly the restored database up.

zThanks for the reminder.

0 Votes 0 ·

Glad to have helped! But I'm even gladder if you abandon TRUSTWORTHY and embrace certificate signing. :-)

0 Votes 0 ·