PrepAway - Latest Free Exam Questions & Answers

Which permission or permissions should you assign to UserC?

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.

PrepAway - Latest Free Exam Questions & Answers

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

8 Comments on “Which permission or permissions should you assign to UserC?”

  1. Testee says:

    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;




    0



    0
    1. Islam says:

      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?




      0



      0
  4. Vivek says:

    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.




    0



    0

Leave a Reply