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 DATABASE [test_db]; GO ALTER DATABASE [test_db] SET RECOVERY FULL; BACKUP DATABASE [test_db] TO DISK = N'C:\temp\test_db.bak' WITH FORMAT; BACKUP LOG [test_db] TO DISK = N'C:\temp\test_db_txlog.bak' WITH FORMAT; GO Create the stored procedure in the test database:
USE [test_db]; GO DROP PROCEDURE IF EXISTS [dbo].[read_log]; GO CREATE 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 [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 Get the values from the FirstLSN and LastLSN values from the log backup:
RESTORE HEADERONLY FROM DISK = N'C:\temp\test_db_txlog.bak'; GO Use those values to run the proc:
DECLARE @start_lsn nvarchar(25) = N'154000000026400001'; DECLARE @end_lsn nvarchar(25) = N'154000000043200001'; EXEC [dbo].[read_log] @start_lsn, @end_lsn, N'C:\temp\test_db_txlog.bak', 1; GO Results from the SELECT statement inside the proc will look like:
| SUSER_SNAME() | USER_NAME() | ORIGINAL_LOGIN |
|---|---|---|
| DOMAIN\user | dbo | DOMAIN\user |
Error produced:
Msg 9011, Level 14, State 1, Procedure dbo.read_log, Line 16 [Batch Start Line 62] 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
If you remove the WITH EXECUTE AS SELF clause from the stored procedure, and re-run it, I get results from the TVF.
Is this something I'm doing wrong, or is there a problem with the EXECUTE AS clause?
