You administer a Microsoft SQL Server 2012 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? Choose all that apply.
A.
The Select permission on Customers
B.
The Execute permission on GetCustomerOrderInfo
C.
The Take Ownership permission on Customers
D.
The Control permission on GetCustomerOrderInfo
E.
The Take Ownership permission on Orders
F.
The Select permission on Orders
Explanation:
The question seems to be missing something. Or the original answer is incorrect. I’ve changed it to what I
believe to be the correct answer. The original answer included “The Select permission on Orders.”, butdue to
ownership chaining, you would only need to give Execute permissions to UserC to access the Orders table
since UserB is the owner.
(BF) – need to test this
Reference:
http://msdn.microsoft.com/en-us/library/ms188676.aspx
http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas
http://sqlservercentral.com/blogs/steve_jones/2012/03/14/ownership-chains-in-sql-server
Correct Answer is b) Execute Permission on GetCustomerOrderInfo if all three objects (Tables and Store Procedure are in the same Schema)because of ownership chaining.
Try the source below.
if one of the tables would be in another Schema the select-righs should be granted
USE Test;
GO
— Create Users
CREATE LOGIN LoginUserA WITH PASSWORD = ‘J345#$)tha’;
GO
CREATE USER UserA FOR LOGIN LoginUserA;
GO
CREATE LOGIN LoginUserB WITH PASSWORD = ‘J345#$)thb’;
GO
CREATE USER UserB FOR LOGIN LoginUserB;
GO
CREATE LOGIN LoginUserC WITH PASSWORD = ‘J345#$)thc’;
GO
CREATE USER UserC FOR LOGIN LoginUserC;
GO
GRANT CREATE TABLE TO UserA;
GRANT CREATE TABLE TO UserB;
GRANT CREATE PROCEDURE TO UserB;
GO
— Create Table Customer
PRINT SUSER_NAME()
PRINT USER_NAME()
EXECUTE AS LOGIN = ‘LoginUserA’;
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
CREATE TABLE dbo.Customers (CustomerID int)
GO
REVERT
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
— Create Table Orders
PRINT SUSER_NAME()
PRINT USER_NAME()
EXECUTE AS LOGIN = ‘LoginUserB’;
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
CREATE TABLE dbo.Orders (OrderID int)
GO
REVERT
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
— Create Procedure GetCustomerOrderInfo
PRINT SUSER_NAME()
PRINT USER_NAME()
EXECUTE AS LOGIN = ‘LoginUserB’;
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
GO
CREATE PROCEDURE GetCustomerOrderInfo
AS BEGIN
Select * from Orders;
Select * From Customers;
END;
GO
REVERT
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
GRANT EXECUTE ON GetCustomerOrderInfo to UserC
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
EXECUTE AS LOGIN = ‘LoginUserC’;
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
Execute GetCustomerOrderInfo
GO
REVERT
GO
DROP PROCEDURE GetCustomerOrderInfo;
DROP TABLE Orders;
DROP Table Customers;
GO
DROP LOGIN LoginUserA;
DROP User UserA;
DROP LOGIN LoginUserB;
DROP User UserB;
DROP LOGIN LoginUserC;
DROP User UserC;
is it A and B the correct answer?
B is correct due to ownership chaining
Permissions on tables are not checked if the tables and the procedure have the the same owner. This is called ownership chaining.
UserB only owns Table Orders and UserA owns Table Customer
So if it is due to the owner ship chaining how would UserC have Select permissions on the Customer table if UserB was the one that created the Store Procedure?
sorry you are correct yes it is B.
Final answer is option A, and B are correct.
B is correct because of ownership chaining. Chaining occurs if:
1. One object refers to another object, like a stored procedure referring to a table.
2. Both objects have the same owner.
Only B is not enough, A should also be the part of answer as Customers table is owned by different owner.
A,B
ab