Exam 70-764 topic 1 question 10 discussion

Actual exam question from Microsoft's 70-764

Question #: 10
Topic #: 1

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.
One instance hosts a user database named HRDB. The database contains sensitive human resources data.
You need to grant an auditor permission to view the SQL Server audit logs while following the principle of least privilege.
Which permission should you grant?

A. DDLAdmin
B. db_datawriter
C. dbcreator
D. dbo
E. View Database State
F. View Server State
G. View Definition
H. sysadmin

Show Suggested Answer

Suggested Answer: F 🗳️
Unless otherwise specified, viewing catalog views requires a principal to have one of the following:
✑ Membership in the sysadmin fixed server role.
✑ The CONTROL SERVER permission.
✑ The VIEW SERVER STATE permission.
✑ The ALTER ANY AUDIT permission.
✑ The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog view).
References: https://technet.microsoft.com/en-us/library/cc280386(v=sql.110).aspx

by TheSwedishGuy at Jan. 27, 2020, 10:25 a.m.

Comments

Submit Cancel

Sumith

3 years, 10 months ago

Answer is ....... H. sysadmin Basically a low privileged user who is able to read the audit logs would be able to know what is being audited and might be able to exploit vulnerabilities. For example, noting that the salary table is not audited, or that updates to salaries are being audited. There are also ways of keeping administrators honest, for example I can configure auditing so that it if fails the SQL Server will be shut down. This is a logged operation that the dbas would need to explain to outside auditors. Granted your DBAs - your sysadmins can read the audit logs, but you need to have a level of trust with them. There are options to provided the level of trust you want to designate to certain people (members of the sysadmin role) and at the same time verify that they are being honest (having SQL Server shut down when there is an audit failure). Azure SQL Database is a provisioned database. It is not a complete server - which is why auditing is provided on a database level. https://social.technet.microsoft.com/Forums/en-US/1488ebbf-e088-4c4c-b728-90f946dd3ca1/permission-required-to-read-audit-logs?forum=sqldatabaseengine

upvoted 1 times

...

Chandra111

4 years, 2 months ago

View Server State.

upvoted 2 times

...

shimon893

4 years, 10 months ago

The VIEW ANY DEFINITION permission provides access to view the server level audit views and VIEW DEFINITION provides access to view the database level audit views. https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

upvoted 1 times

...

MelKr

4 years, 11 months ago

Just tried this in my test environment and no, this permission is nut sufficient to view the audit logs. When using the funcion "fn_get_audit_file" I get the message "CONTROL SERVER permission was denied on object 'server', database 'master'." So according to the given answers "Sysadmin" is correct.

upvoted 3 times

...

TheSwedishGuy

5 years, 2 months ago

It almost seems too obvious that the "View Server State" permission is able to View Server audit logs.

upvoted 1 times

...

Exam 70-764 All Questions

View all questions & answers for the 70-764 exam

Exam 70-764 topic 1 question 10 discussion

Comments

Sumith

Chandra111

shimon893

MelKr

TheSwedishGuy

SY0-701