Forum Discussion
Select from sys.fn_get_audit_file without CONTROL SERVER
- 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-and-azure-sql-to/ba-p/3607507#:~:text=This%20is%20very%20useful%20for%20Security%20Auditors%20who%20should%20not%20have%20the%20ability%20to%20also%20change%20existing%20SQL%20Auditing%20definitions.
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?
- 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-and-azure-sql-to/ba-p/3607507#:~:text=This%20is%20very%20useful%20for%20Security%20Auditors%20who%20should%20not%20have%20the%20ability%20to%20also%20change%20existing%20SQL%20Auditing%20definitions.- 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
Altinakaraulli "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):
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.