Exam 70-764 topic 1 question 112 discussion

Actual exam question from Microsoft's 70-764

Question #: 112
Topic #: 1

You are the lead database administrator (DBA) of a Microsoft SQL Server 2016 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 SQL statement or statements should you use? Choose all that apply.

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

Show Suggested Answer

Suggested Answer: BCF 🗳️

by TheSwedishGuy at Jan. 28, 2020, 12:19 p.m.

Comments

Submit Cancel

TheSwedishGuy

5 years, 3 months ago

Create the server role with the default permissions assigned to securityadmin. Then add the login to this role. Since it is security admin, it can already grant, deny and revoke permissions for logins. It can't, however, view the server state or view the databases. Therefore, you need to grant those permissions to the role.

upvoted 3 times

...

Exam 70-764 All Questions

View all questions & answers for the 70-764 exam

Exam 70-764 topic 1 question 112 discussion

Comments

TheSwedishGuy

SY0-701