SOLVED

Select from sys.fn_get_audit_file without CONTROL SERVER

Copper Contributor

Hi

We are pulling MS SQL Server audit records using sys.fn_get_audit_file

Server Login MYUSER

Not mapped to any non-system database

Related DB User on database "master" is: myuser

Granted CONTROL SERVER and (of course) Connect

 

Can execute:

select * from sys.fn_get_audit_file(<path>, default, default)


Problem:
Granting CONTROL SERVER to a user that needs to only SELECT the audit records - and doing nothing else - looks too much.

So we tried:

Revoke CONTROL SERVER form Server Login MYUSER

On database "master", grant DB User “myuser” SELECT on sys.fn_get_audit_file

 

Now the Server Login MYUSER cannot execute:

select * from sys.fn_get_audit_file(<path>, default, default)

 

Error Message:

Msg 300, Level 14, State 1, Line 1

CONTROL SERVER permission was denied on object 'server', database 'master'.

Msg 297, Level 16, State 1, Line 1

The user does not have permission to perform this action.

Question:
Is it possible to SELECT from sys.fn_get_audit_file without having the CONTROL SERVER ?

best regards
Altin

6 Replies
Create a job that copy over the "get audit" result to a user defined database/table.
That is one way, but it will generate extra load on the system.

By your response I conclude that what I am asking for is not possible.
I guess this is because some MS SQL Server system's object require the CONTROL SERVER as a built-in-code requirement.
Something like in this link:
http://andreas-wolter.com/en/control-server-vs-sysadmin-sa/
Am I correct on this?

best response confirmed by akaraulli (Copper Contributor)
Solution
From your error message I'll assume you're not on SQL Server 2022.
If that's the case, it would appear you're out of luck.

What you want is possible in SQL Server 2022, due to a more granular permission set, as well as splitting ALTER and VIEW permissions.
On SQL Server 2022 you'd get the following error:
VIEW SERVER SECURITY AUDIT permission was denied on object 'server', database 'master'.

This would then of course be resolved by:
Grant VIEW SERVER SECURITY AUDIT to MyUser

See this blog post for more information:
https://techcommunity.microsoft.com/t5/sql-server-blog/new-granular-permissions-for-sql-server-2022-....
Thank You Silversolem
Indeed I am not on SQL Server 2022. I have one 2017 and another 2019.
I did read the article whose link you posted, and it is what I was asking for - at least partially.
I say "partially", because at this point I have one more question.

Under "VIEW SERVER SECURITY AUDIT", article says:
This is very useful for Security Auditors who should not have the ability to also change existing SQL Auditing definitions. With this permission, the Audit Log can be read via the system function sys.fn_get_audit_file (Transact-SQL) ....

It is fine that they cannot change any audit. But in my case I am not talking for "Security Auditors", but just for pulling the audit trail (sys.fn_get_audit_file) into some external log monitoring system/SIEM.

Would it be possible that MyUser should not even view the audit settings? Indeed, the only thing I want for this account is to logon and select the audit trail - possibly nothing else.
I guess this is not possible even in SQL Server 2022 - correct?
Will it be considered in the future versions ?

best regards
Altin



@akaraulli "Security Auditors" as in "accounts that should be able to run sys.fn_get_audit_file".

In testing, using a sysadmin account and an account with "View Server Security Audit" for the following commands:

select * from sys.server_audits
select * from sys.fn_get_audit_file('path', default, default)

I get this output (Auditor on right):
Screenshot 2024-04-09 164301.png

 

As you can see, the "Auditor" does not have access to the audit specifications, yet can access the audit log file.

In a SQL Server 2022 instance, your SIEM should be more than happy to access the audit file using this permission.

I have also experienced SIEMs that are poorly able to handle data from an audit file. In that case you could consider logging to the Windows Security Log instead. It's not unlikely that your SIEM already reads the Windows Security Log.
It does require additional permissions for the SQL Engine service account and has other limitations.
See: Write SQL Server Audit events to the Security log - SQL Server | Microsoft Learn

In any case, the SIEM would then _not_ require access in the SQL Server instance at all, so no audit configuration could be read.

Also, I am not affiliated with Microsoft, so I do not have any insight into the changes coming with future editions of SQL Server.
1 best response

Accepted Solutions
best response confirmed by akaraulli (Copper Contributor)
Solution
From your error message I'll assume you're not on SQL Server 2022.
If that's the case, it would appear you're out of luck.

What you want is possible in SQL Server 2022, due to a more granular permission set, as well as splitting ALTER and VIEW permissions.
On SQL Server 2022 you'd get the following error:
VIEW SERVER SECURITY AUDIT permission was denied on object 'server', database 'master'.

This would then of course be resolved by:
Grant VIEW SERVER SECURITY AUDIT to MyUser

See this blog post for more information:
https://techcommunity.microsoft.com/t5/sql-server-blog/new-granular-permissions-for-sql-server-2022-....

View solution in original post