You are the database administrator for a banking firm and maintain all the SQL Server 2008 databases of the firm.
The company stores customer-related data in the Cust_details table in a database named Customers.
You havecreated several roles in the database to effectively manage the permissions granted to database users.
The permissions granted to the different roles in the Cust_details table are as follows:

John is a new user in the audit department.
To create reports on the data in the Cust_details table, John isrequired to view the data in this table.
John should be able to add new records to the table and update theexisting data in the table.
You must ensure that John only has the required privileges.
You want to accomplish thiswith the least administrative effort.

What should you do? (Choose all that apply. Each correct answer presents a portion of the solution.)

A.
Add John to the Clerks role.

B.
Add John to the Auditors role.

C.
Add John to the Managers role.

D.
Grant John UPDATE permission on the table.

E.
Grant John INSERT permission on the table.

F.
Revoke DELETE permission on the table from John.

Explanation:

You should add John to the Auditors role and grant John UPDATE permission on the table. In this scenario,John must be able to perform select, insert, and update operations on the database. Therefore, John should begranted the appropriate permissions to do so. The Auditors role is granted SELECT and INSERT permissions onthe table. Therefore, you are only required to grant the UPDATE permission to John to fulfill the requirement inthis scenario with the least administrative effort. You should not add John to the Clerks role. Adding John to the Clerks role will grant John SELECT permissionon the table. Therefore, in this scenario, you would be required to grant two additional privileges to John. This willinvolve additional administrative effort compared to granting John the Auditors role and UPDATE permission onthe table. You should not add John to the Managers role because this will grant additional permissions to John. The Managers role has been granted SELECT , UPDATE , and DELETE permissions on the table. Granting John the Managers role will grant John DELETE permission on the table, which is not required in this scenario. You should not grant John INSERT permission on the table. In this scenario, the least administrative effort would be required by granting John the Auditors role, and additional UPDATE permission on the table. The Auditors role has INSERT permission on the table. You should not revoke DELETE
permission on the table from John. In this scenario, the least administrative effortwill be involved in granting the Auditors role, and this role does not have DELETE permission on the table.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage database permissions.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > GRANT (Transact-SQL) > GRANT Database Permissions (Transact-SQL)