Forum Discussion
akaraulli
Feb 29, 2024Brass Contributor
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 ...
- Apr 09, 2024From 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.
olafhelper
Feb 29, 2024Bronze Contributor
Create a job that copy over the "get audit" result to a user defined database/table.