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 fixed server role on SQL_corp will ensure that John is able to manage permissions for the SQL_corp server. Adding John’s account to the db_securityadmin fixed database role on the Sales database on SQL_public will ensure that 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 membership would allow John to configure all server settings, including all permissions on all databases. This is more permission 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 the databases on the server. You should not add John’s account to the db_securityadmin fixed database role on all databases on both servers. 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 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity and Access Control > Database-Level Roles TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity and Access Control > Server-Level Roles