GRANT select on function FN_DUMP_DBLOG in SQL Server without GRANTing sysadmin

Question

I'm trying to GRANT permission for an SQL login to access the functions fn_db_log and fn_dump_dblog but GRANTing SELECT on the function still fails with error:

Msg 9010, Level 14, State 1, Line 3
User does not have permission to query the virtual table, DBLog. 
Only members of the sysadmin fixed server role and the db_owner fixed database role have this permission

Below are the steps I followed:

create database duumy;
GO
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [duumy]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [duumy]
GO
EXEC sp_addrolemember N'db_owner', N'test'
GO
USE [master]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [msdb]
GO
CREATE USER [test] FOR LOGIN [test]
GO

use master;
GRANT SELECT ON FN_DBLOG TO test;
GRANT SELECT ON FN_DUMP_DBLOG TO test;
GRANT VIEW SERVER STATE TO test;

I'm not sure what I'm missing here. Please assist.

Answer 1

Not sure how this would be affected (if at all) by Azure SQL Database, but in general you should use Module Signing to grant higher level permissions in the most targeted, secure manner.

You would create a stored procedure or multi-statement TVF containing the SELECT ... FROM sys.fn_... command. Then, you would sign the stored procedure or TVF with an Asymmetric Key or Certificate.

If you just need to make use of the db_owner role, then you can probably get away with an Asymmetric Key, then create a User from that Asymmetric Key, and finally add that User to the db_owner role.

A Certificate works in both cases. But, if you need to make use of the sysadmin server role, then you need a Certificate. So you would create a Certificate in the DB containing the stored procedure or TVF, sign that module, then create that same Certificate in master using only the public key, then create a Login from that Certificate, and finally add that Login to the sysadmin server role.

For details, please see the following posts:

• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level

• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level