GRANT select on function FN_DUMP_DBLOG in SQL Server without GRANTing sysadmin

by Ramya   Last Updated September 14, 2018 04:06 AM

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.



Related Questions


Azure SQL Synonyms with On-Premise

Updated November 06, 2018 06:06 AM

Migrating my sql server to Azure cloud

Updated April 06, 2017 14:06 PM