2

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.

| improve this question | | | | |
  • Both fn_dblog and fn_dump_dblog are undocumented command and there is hardly any information which says what rights are required. I believe ONLY a member of sysadmin fixed server role have permission to run it. Be very careful when using fn_dump_dblog it creates hidden scheduler. I would not advise you to use these commands on production – Shanky Sep 14 '18 at 6:33
  • You're going to need to use module signing to achieve what you'd like. docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/… – Sean Gallardy - Retired User Sep 14 '18 at 10:32
4

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:

| improve this answer | | | | |
  • The 2 links from your blog are very well written and helpful. Thanks ! – Kin Shah Sep 14 '18 at 14:31
  • @Solomon Rutzky Thank you for your response. The problem is it's not me who is directly issuing the "select" on fn_dump_dblog function. I am using an application called "DMS" in order to replicate data from On-prem SQL server to RDS which executes this funtion behind the scenes – Ramya Sep 15 '18 at 3:24
  • So a direct way to GRANT a select on fn_dump_dblog without sysadmin would be appreciated! – Ramya Sep 15 '18 at 3:26
  • And I couldn't do something like below: ADD SIGNATURE TO sys.fn_dump_dblog BY CERTIFICATE [Permission_fndumpdblog] WITH PASSWORD = 'Password@123'; Msg 15151, Level 16, State 1, Line 1 Cannot alter the object 'sys.fn_dump_dblog', because it does not exist or you do not have permission. – Ramya Sep 15 '18 at 3:41
  • @Ramya I'm not sure that such a thing is possible to do directly. These two functions actually exist in the hidden, read-only DB mssqlsystemresource. I see that the error message says that being in db_owner should work, but I can't get that to work either. I will try a few other things and let you know if I can find a way. – Solomon Rutzky Sep 17 '18 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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