Question 1
You administer a Microsoft SQL Server database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables.
You create a new user named UserC.
You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC.
Which permission or permissions should you assign to UserC? Each correct answer presents part of the solution.
The Select permission on Customers
The Execute permission on GetCustomerOrderInfo
The Take Ownership permission on Customers
The Control permission on GetCustomerOrderInfo
The Take Ownership permission on Orders
The Select permission on Orders
Correct answer: B
Explanation:
How Permissions Are Checked in a Chain When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated. Due to ownership chaining, you would only need to give Execute permissions to UserC to access the Orders table since UserB is the owner. References: https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx
How Permissions Are Checked in a Chain
When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.
Due to ownership chaining, you would only need to give Execute permissions to UserC to access the Orders table since UserB is the owner.
References: https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx