Forum Discussion
Solved
Select from sys.fn_get_audit_file without CONTROL SERVER
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 ...
- 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.
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?- 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
Altin
