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.