Exam 70-764 topic 1 question 321 discussion

Actual exam question from Microsoft's 70-764

Question #: 321
Topic #: 1

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.

Show Suggested Answer

Suggested Answer: BC 🗳️
Incorrect Answers:
A: There is no Sales Schema.
D: No need for DELETE permissions.

by Guest at Nov. 25, 2019, 10:45 p.m.

Comments

Submit Cancel

tapsAch

4 years, 9 months ago

A is not correct. They have specifically mentioned that only VIEW and SP need permission. If we apply SELECT and EXECUTE on schema then SELECT applies to TABLE and EXECUTE may apply to FUNCTION like other objects so B and C are correct answers.

upvoted 1 times

Cococo

4 years, 8 months ago

yep, B and C are correct, here is how to add an AD group to a SQL Server - https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server

upvoted 1 times

...

TheSwedishGuy

5 years, 4 months ago

A and C

upvoted 2 times

...

Guest

5 years, 6 months ago

Don't agree with A being incorrect as the text states: You have a database named Contoso that has a schema named Sales.

upvoted 1 times

jolsca

4 years, 9 months ago

It cannot be A, because the schema has many objects, All objects in the database have the same owner. When granting permissions to the database, you should use the principle of least privilege.

upvoted 2 times

...