Exam 70-462 topic 3 question 1 discussion

Actual exam question from Microsoft's 70-462

Question #: 1
Topic #: 3

You are the lead database administrator (DBA) of a Microsoft SQL Server environment.
All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server.
You need to create a server role named SpecialDBARole that can perform the following functions:
✑ View all databases.
✑ View the server state.
✑ Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary.
Which three SQL statements should you use? Each correct answer presents part of the solution.

A. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
B. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
C. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
D. CREATE SERVER ROLE [SpecialDBARole]; AUTHORIZATION serveradmin;
E. GRANT VIEW DEFINITION TO [SpecialDBARole];
F. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
G. GRANT VIEW ANY DATABASE TO [SpecialDBARole];
H. CREATE SERVER ROLE [SpecialDBARole]; AUTHORIZATION sysadmin ABC

Show Suggested Answer

Suggested Answer: Explanation 🗳️
A: View all databases. View the server state.
B: Assign GRANT, DENY, and REVOKE permissions on logins.
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.
C: You need to add DOMAIN\JrDBAs to the server role.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles
References:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175892(v=sql.105)

by mr_corte at Nov. 26, 2019, 9:05 a.m.

Comments

Submit Cancel

mr_corte

5 years, 5 months ago

Answer: A-B-C

upvoted 3 times

...

Exam 70-462 All Questions

View all questions & answers for the 70-462 exam

Exam 70-462 topic 3 question 1 discussion

Comments

mr_corte

SY0-701