You are a database administrator at Contoso, Ltd. You are preparing to move a global sales application from a development environment to a production environment.

You have a database named Contoso that has a schema named Sales. All objects in the database have the same owner. The schema has a large number of views and stored procedures. None of the stored procedures perform IDENTITY_INSERT operations or dynamic SQL commands. You create all views by using the WITH SCHEMABINDING option.

All employees in the sales division are members of an Active Directory Domain Services (AD DS) security group named Contoso\Sales.

The following database objects are the only objects queried when a user from Contoso\Sales is using the application.

Views

SalesReport

SalesInvoice

Stored procedures

InvoiceExecute – performs read/write operations

InvoiceSearch – performs read-only operations

When granting permissions to the database, you should use the principle of least privilege.

You create a new user-defined database role named SalesRole and add Contoso\Sales as a member of SalesRole.

You need to grant all employees in the Sales division permission to use the views and stored procedures.

Which two solutions will meet the requirements? Each correct answer presents a complete solution.

A. Grant the SELECT permission on the Sales schema to SalesRole. Grant the EXECUTE permission on the Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.

B. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to Contoro\Sales.

Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to Contoso\Sales.

C. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole. Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole.

D. Grant the SELECT permission on Sales.SalesReport and Sales.SalesInvoice to SalesRole. Grant the EXECUTE permission on Sales.InvoiceExecute and Sales.InvoiceSearch to SalesRole. Grant the SELECT permission on all tables referenced by Sales.SalesReport, Sales.SalesInvoice, and Sales.InvoiceSearch to SalesRole.

Grant the SELECT, INSERT, UPDATE, and DELETE permissions on all tables referenced by Sales.InvoiceExecute to SalesRole.

Incorrect Answers:

A: There is no Sales Schema.

D: No need for DELETE permissions.