SQL Server Security

Question

The database has a table named Customers owned by User A and another table named Orders owned by User B. There is a stored procedure named GetCustomerOrderInfo owned by User B. GetCustomerOrderInfo selects data from both tables. You create a new user named User C. You need to ensure that User C can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to User C. Which permission or permissions should you assign to User C?

Answer 1

Hi @Chaitanya Kiran ,

Premissions on tables are not checked if the tables and the produre have the same owner. this is called ownership chaining

1. A user tries to access an object that makes a reference to another object, such as, the user tries to execute a stored procedure that accesses other objects or a SELECT from a view that accesses other tables.
   2.The user has access to the first object
   3.Both objects have the same owner
   if all of the above conditions are true, ownership chaining occurs

So for User C to call the GetCustomerOrderInfo stored procedure, he needs the GetCustomerOrderInfo execute permission(2)
At the same time, GetCustomerOrderInfo selects data from both Customers and Orders tables, User C needs to check permissions on Customers table, so you need The Select permission on Customers(1)

https://stackoverflow.com/questions/19517251/sql-server-user-permissions-on-stored-procedure-and-underlying-tables#comment39534182_19517251

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This is a test question.

I suggest you read:
https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-ownership-chains-and-context-switching?view=sql-server-ver15#3-access-data-through-the-stored-procedure