WITH EXECUTE AS SELF returning an error when running sys.fn_dump_dblog

I'm attempting to configure a stored procedure to EXECUTE AS a user with sysadmin privileges to allow a non-privileged user to obtain results from the undocumented TVF sys.fn_dump_dblog.

I'm creating the procedure as a member of the sysadmin role, however when I subsequently attempt to run the procedure using the WITH EXECUTE AS SELF clause, I get the following error:

Msg 9011, Level 14, State 1, Procedure dbo.read_log, Line 16 [Batch Start Line 65]
User does not have permission to query backup files with the virtual table DBLog. Only members of the sysadmin fixed server role has this permission

This seems strange to me, since I am a member of the sysadmin server role, and specifying the WITH EXECUTE AS SELF clause in the definition of the procedure should cause the procedure to be executed using my user account. According to the Learn site:

EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module. The actual user ID of the person creating or modifying the modules is stored in the execute_as_principal_id column in the sys.sql_modules or sys.service_queues catalog view.

If I remove the WITH EXECUTE AS SELF clause from the definition of the procedure, the TVF returns results as expected.

Here's a minimal, complete, and verifiable example from SQL Server 2019.

First, prove that we're a member of the sysadmin role:

USE [master];
GO
SELECT 
      [roles].[name]
FROM sys.server_principals [members]
    INNER JOIN sys.server_role_members [srm] ON members.[principal_id] = srm.[member_principal_id]
    INNER JOIN sys.server_principals [roles] ON srm.[role_principal_id] = roles.[principal_id]
WHERE members.[sid] = SUSER_SID();
GO

name

sysadmin

Next, create a test database where we'll host the stored procedure:

```
IF DB_ID(N'test_db') IS NOT NULL
BEGIN
ALTER DATABASE [test_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END;
DROP DATABASE IF EXISTS [test_db];
GO
CREATE DAT

I've added select from sys.login_token to help with the permissions debugging.
USE master
go
CREATE OR ALTER PROCEDURE dbo.read_log
(
@start_lsn nvarchar(25)
, @end_lsn nvarchar(25)
, @log_file nvarchar(260)
, @seq_num int
)
WITH EXECUTE AS self
AS
BEGIN
SELECT
[SUSER_SNAME()] = SUSER_SNAME()
, [USER_NAME()] = USER_NAME()
, ORIGINAL_LOGIN = ORIGINAL_LOGIN();

SELECT
*
FROM sys.login_token AS lt

SELECT fdd.*
FROM sys.fn_dump_dblog
(
@start_lsn
, @end_lsn
, N'DISK'
, @seq_num
, @log_file
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL
) fdd;
END;
GO


If I keep the EXECUTE as self, we can see that the sa and sysadmin tokens have DENY ONLY in the usage column.

When I remove or comment out the EXECUTE as self, we can see a lot more login tokens and also the usage changes to GRANT OR DENY

The documentation you've linked says so (emphasis mine)

EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module. The actual user ID of the person creating or modifying the modules is stored in the execute_as_principal_id column in the sys.sql_modules or sys.service_queues catalog view.

It mentions user which is a database-level principal. The procedure needs a server-level principal (login) permission. It seems like EXECUTE AS SELF limits itself to the database scope.
Alternative way to grant access

If you want to grant access to other users without sysadmin permissions, you can use module signing to sign the procedure. One way is to move the procedure into the master database:

• Create a certificate in the master database - that's because server logins live there and need line of sight to the certificate

USE master
CREATE CERTIFICATE SaPermissionCert
ENCRYPTION BY PASSWORD = 'password123$'
WITH
SUBJECT = 'Used to grant sa permissions to a module'
, EXPIRY_DATE = '99991231'


• Create a login from the certificate and grant it permissions. I'm using sysadmin server role, but it's possible lesser permissions will get you there

CREATE LOGIN SaPermissionUser FROM CERTIFICATE SaPermissionCert
ALTER SERVER ROLE sysadmin ADD MEMBER SaPermissionUser


• Sign your procedure with the certificate. The procedure must be in the master database because that's where the certificate lives.

USE master
ADD SIGNATURE TO dbo.read_log
BY CERTIFICATE SaPermissionCert WITH PASSWORD = 'password123$'


Now you only need to grant permissions to execute the proc to your end users as you normally would.

Don't forget that any changes to the procedure require the signature to be reapplied.

You can avoid moving the procedure to master by copying the certificate as Solomon Rutzky describes in Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level.

To avoid issues with password management, use the approach Erland Sommarskog describes in Packaging Permissions in Stored Procedures. This uses a disposable random password that doesn't need to be stored or otherwise managed in your deployment environment. The certificate and login are fully regenerated each time the procedure is signed. You can find his example script here.

StackExchange Database Administrators Q#327267, answer score: 11