Exam 70-764 topic 1 question 44 discussion

Actual exam question from Microsoft's 70-764

Question #: 44
Topic #: 1

HOTSPOT -
You manage a Microsoft SQL Server instance. You have a user named User1.
You need to grant the minimum permissions necessary to allow User1 to review audit logs.
For each action, which option should you use? To answer, select the appropriate options in the answer area.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: securityadmin -
To access log files for instances of SQL Server that are online, this requires membership in the securityadmin fixed server role.
Box 2: sys.server_audit_specifications
sys.server_audit_specifications contains information about the server audit specifications in a SQL Server audit on a server instance.

by TheSwedishGuy at Jan. 24, 2020, 11:29 a.m.

Comments

Submit Cancel

MelKr

5 years ago

Security Admin does not allow to view audit metadata nor audit logs. It needs "Control Server" to view audit logs and "View any Definition" or "Alter any server audit" to access the audit metadata. Therefore, no answer would be correct in the first box. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-audit-specifications-transact-sql?view=sql-server-2016

upvoted 1 times

KC

4 years, 9 months ago

Control Server is different than the role assigned. See the permissions here: https://docs.microsoft.com/en-us/sql/relational-databases/logs/log-file-viewer-f1-help?redirectedfrom=MSDN&view=sql-server-ver15

upvoted 2 times

...

TheSwedishGuy

5 years, 4 months ago

Since audits have to do with security, it's reasonable that the fixed server role should be called "securityadmin".

upvoted 1 times

...

TheSwedishGuy

5 years, 4 months ago

Box 2: The syntax is important. Since it is regarding audits, this made clear in sys.server_audit_specifications. It's not regarding the file audit, so it is not sys.server_file_audit.

upvoted 1 times

KC

4 years, 9 months ago

Box 2: It's kind of a tricky question. It almost leads you to believe you need the syntax to view the audit log. But Microsoft recommends using the log viewer or an option not listed for that. "Microsoft recommends viewing the audit log by using the Log File Viewer. However, if you are creating an automated monitoring system, the information in the audit file can be read directly by using the sys.fn_get_audit_file (Transact-SQL) function." https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/view-a-sql-server-audit-log?view=sql-server-2016

upvoted 1 times

KC

4 years, 9 months ago

Box 2 cont.: So the question is probably asking for the syntax to set up parameters for user to view the audit. I don't think any of the answers are great. But I believe sys.server_permissions would be the syntax to view whether the user has permissions to view the audit log. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql?view=sql-server-ver15

upvoted 1 times

...