Administering a SQL Database Infrastructure — Question 34

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.

Answer options

• 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.

Correct answer: B, C

Explanation

Option B correctly grants SELECT permissions directly to the Contoso\Sales group for the specified views and EXECUTE permissions for the stored procedures, ensuring all employees can access them. Option C also works by granting the necessary permissions to the SalesRole, which the group is a member of. Options A and D are incorrect as they either do not directly provide access to the group or include unnecessary permissions beyond what is needed, which goes against the principle of least privilege.