9

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?

1 Answer 1

11

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.

enter image description here

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

enter image description here

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:

  1. 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'
  1. 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
  1. 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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.