You are the SQL administrator for your company. You manage all of the SQL Server 2008 computers for yourcompany. All of your databases use Windows Authentication.
Because of recent security issues with human resources information, you have been asked to grant a user inhuman resources the right to remove access for Windows logins to the HR database. You do not want to grantthe user more permissions than required. What should you do?

A.
Add the user’s account to the db_accessadmin fixed database role for the HR database.

B.
Create a new database role that allows its members to remove access for Windows logins to the HR database. Add the user to the new database role.

C.
Add the user’s account to the db_securityadmin fixed database role for the HR database.

D.
Add the user’s account to the db_denydatareader and db_denydatawriter fixed database roles for the HR database.

Explanation:

You should create a new database role that allows its members to remove access for Windows logins to the HR database and add the user to the new database role. This will ensure that the user can remove access forWindows logins to the HR database.

You should not add the user’s account to the db_accessadmin fixed database role for the HR database. Members of this role can add and remove database access for Windows logins, Windows groups, and SQLlogins. You only wanted the user to be able to remove Windows logins. You should not add the user’s account to the db_securityadmin fixed database role for the HR database. Members of this role can add and remove role members and manage database permissions. However, membersof this role cannot remove access for Windows logins.You should not add the user’s account to the db_denydatareader and db_denydatawriter fixed database rolesfor the HR database. The db_denydatareader fixed database role prevents users from reading data in thedatabase. The db_denydatawriter fixed database role prevents users from writing data to the database.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage users and database 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