1

I have a non-sysadmin user which must be provided with an ability to view "server audit logs"

Tried to grant below permissions to this login / user:

grant ALTER ANY server AUDIT  to [login]
grant ALTER ANY database AUDIT  to [user]

But if he runs below, he is still getting error message:

select top 100 * from fn_get_audit_file(Path:\\...)

Msg 297, Level 16, State 1, Line 44
The user does not have permission to perform this action.

So, is there an ability to grant non-admin user permission to view audit logs ?

Thanks!

3
  • The doc pages are fairly helpful. Why don’t you spend time there?
    – clifton_h
    Commented Mar 6, 2018 at 0:51
  • @clifton_h, doc pages suggest granting CONTROL SERVER permission, but I do not want to do this... is there a way to grant more granular permission - just to view the audit logs, but not the CONTROL SERVER ? Commented Mar 6, 2018 at 9:23
  • 1
    Well, yeah. Use a wrapper procedure to get the information. You use an EXECUTE AS on the proc, grant this user to execute the proc, and your done. No messy permissions to grant. 😋
    – clifton_h
    Commented Mar 6, 2018 at 15:32

1 Answer 1

1

Reference:

Microsoft recommends viewing the audit log by using the Log File Viewer. However, if you are creating an automated monitoring system, the information in the audit file can be read directly by using the sys.fn_get_audit_file (Transact-SQL) function. Reading the file directly returns data in a slightly different (unprocessed) format. See sys.fn_get_audit_file for more information.

In order to use sys.fn_get_audit_file users requires the CONTROL SERVER permission.

You can deny privilege on a principle who has been assigned CONTROL SERVER permission which is not possible in case of SYSADMIN.

More about this:

Security Questions: Difference between sysadmin and CONTROL SERVER Permission by Jason Strate.

2
  • is there any way to grant access to audit logs via sys.fn_get_audit_file function, without granting CONTROL SERVER permission? Commented Mar 6, 2018 at 9:20
  • 1
    I do not know. One suggestion is to use a development server where you are less concerned with privilege. Commented Mar 6, 2018 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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