Administering a SQL Database Infrastructure — Question 129

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.

Answer options

• 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];

Correct answer: B, C, F

Explanation

The correct answers are B, C, and F. Option B adds the DOMAIN\JrDBAs group to the newly created role, which is essential for granting them the specified permissions. Option C correctly authorizes the role under securityadmin, allowing it to manage permissions. Option F grants the necessary permissions to view server state and databases. The other options either do not add members or assign the role to inappropriate authorizations.