070-458 Real Exam Prep DEMO

Question 1: 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. Add EXECUTE AS OWNER to the stored procedure. Add all users to the db_datareader role.
B. Add all users to the db_datawriter role. Add all users to the db_datareader role.
C. Create a new database role. Grant EXECUTE permission on p_AddInventory to the newrole. Add
all users to the role. Add all users to the db_datareader role.
D. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role.
Grant VIEW DEFINITION to to the role. Add all users to the role.
Correct Answer: A
Explanation:
In SQL Server you can define the execution context of the following user-defined modules: functions
(except inline table-valued functions), procedures, queues, and triggers. By specifying the context in
which the module is executed, you can control which user account the Database Engine uses to
validate permissions on objects that are referenced by the module. This provides additional
flexibility and control in managing permissions across the object chain that exists between
user-defined modules and the objects referenced by those modules. Permissions must be granted to
users only on the module itself, without having to grant them explicit permissions on the referenced
objects. Only the user that the module is running as must have permissions on the objects accessed
by the module.
Reference: EXECUTE AS Clause (Transact-SQL) You administer a Microsoft SQL Server 2012 database.