Exam 70-765 topic 3 question 52 discussion

Actual exam question from Microsoft's 70-765

Question #: 52
Topic #: 3

You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.
Users need to be able to SELECT from all tables in the database and execute the stored procedure.
You need to grant only the necessary permissions.
What should you do?

A. Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
B. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
C. Add all users to the db_owner role.
D. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datareader role.

Show Suggested Answer

Suggested Answer: D 🗳️
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
References:
https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

by rattila at Dec. 15, 2020, 10:15 a.m.

Comments

Submit Cancel

blues198

4 years, 5 months ago

The D is correct, just test in my enviroment: https://ibb.co/LZnQx2H

upvoted 1 times

blues198

4 years, 5 months ago

Wrong link, this is the correct one: https://ibb.co/Fm0Z56h

upvoted 1 times

...

rattila

4 years, 6 months ago

db_datareader role don't execute store procedures. The A is correct

upvoted 2 times

Haidardba

4 years, 5 months ago

If you simply excute SELECT statements, the db_datareader role should fit for executing your stored procedures. The db_datawriter is the role, that is eligible to create them. https://stackoverflow.com/questions/13142114/roles-needed-for-executing-stored-procedure-in-sql-server-authentication#:~:text=It%20depends%20on%20the%20action,is%20eligible%20to%20create%20them.

upvoted 1 times

Haidardba

4 years, 5 months ago

https://stackoverflow.com/questions/13142114/roles-needed-for-executing-stored-procedure-in-sql-server-authentication

upvoted 1 times

...