which role should you add John’s account?

You are the SQL administrator for your company. You have two SQL Server 2008 computers named SQL_corp and SQL_public.
Multiple databases reside on these two servers.
A new user named John has been hired in your department.
John must be able manage permissions for the SQL_corp server.
He should also be able to manage permissions on the Sales database on the SQL_public computer.
You should not grant John any permissions he does not need. To which role should you add John’s account?

A.
the sysadmin role on both servers

B.
the securityadmin role on both servers

C.
the securityadmin role on SQL_corp and the db_securityadmin role on the Sales database on SQL_public

D.
the db_securityadmin role on all databases on both servers

Explanation:

You should add John’s account to the securityadmin fixed server role on SQL_corp and the db_securityadmin fixed database role on the Sales database on SQL_public . Adding John’s account to the securityadmin fixedserver role on SQL_corp will ensure that John is able to manage permissions for the SQL_corp server. AddingJohn’s account to the db_securityadmin
fixed database role on the Sales database on SQL_public will ensurethat John can manage permissions on the Sales database. You should not add John’s account to the sysadmin
fixed server role on both servers. This role membershipwould allow John to configure all server settings, including all permissions on all databases. This is morepermission than John requires to complete his duties. You should not add John’s account to the securityadmin fixed server role on both servers. On the SQL_public server, John should only be able to manage permissions for a single database, Sales . Adding his account to the securityadmin fixed server role on the SQL_public server will allow him to manage permissions for all thedatabases on the server. You should not add John’s account to the db_securityadmin fixed database role on all databases on bothservers. John only needs to manage the permissions for the Sales database on SQL_public .

Objective:
Managing SQL Server Security

Sub-Objective:
Manage logins and server roles.

References:
TechNet > TechNet Library > Server Products and Technologies >SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity andAccess Control > Database-Level Roles TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity andAccess Control > Server-Level Roles