Forum Discussion
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 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
- 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.
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?- 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.
