Administering Microsoft SQL Server 2012/2014 Databases — Question 16

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.

Answer options

• 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

Correct answer:

Explanation

The correct answers are A, C, and B. Statement A grants the necessary permissions to view server state and databases to the SpecialDBARole. Statement C adds DOMAIN\JrDBAs to this newly created role, while statement B creates the role with the appropriate authorization. The other options either do not fulfill the requirement or provide unnecessary privileges.