You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 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];
The Role must be created so answer (A),(C) or (E) is required – the only AUTHORIZATION choice which has no additional permissions beyond what is required for the new role is securityadmin, so the first selection is (C).
Then the additional permissions required are View Server State and View Databases, so those are added to the role by (F). Finally the group must be added to the role using (B). So (C),(F),(B) is the answer.
Eli is right. Here’s a link for roles and their permissions.
https://technet.microsoft.com/en-us/library/ms175892(v=sql.90).aspx
B – C – F
B – C – F