Administering a SQL Database Infrastructure — Question 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?

Answer options

• 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_Add!nventory to all users. Add all users to the db_datareader role.

Correct answer: D

Explanation

The correct answer is D because it grants users the EXECUTE permission on the stored procedure and adds them to the db_datareader role, allowing them to SELECT from all tables. Options A and B do not provide the necessary SELECT permissions on tables, while option C gives excessive permissions by making users db_owner, which is not necessary for their task.