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.