Being a DBA or DBE, understanding how Ownership Chains work and how impersonate another principal for changing the execution context are really important. This topic was discussed while I was conducting the course 6232A on last Saturday, thought to blog on it as it would be useful to you all.
What is Ownership Chain?
When we perform an action with set of statements, objects used are accessed sequentially and this sequence is called as a Chain. Once the chain is established, permissions on objects in the chain are evaluated differently, not the way it checks permissions when they are separately accessed. If the objects accessed in the chain are owned by a same owner, Ownership Chain is established. Within the ownership chain, SQL Server checks the permission for the first object and skips checking on others. This is the advantage of Ownership Chain because it offers slight performance benefit skipping permission checks on other objects. If SQL Server finds a broken ownership chain, it starts evaluating permission on them.
What is Impersonation?
SQL Server Impersonation means, allowing to change the execution context with another principal. Simply, perform some actions (execute set of queries) using someone else privileges. This can be done explicitly by using the stand-alone EXECUTE AS statement or implicitly by using the EXECUTE AS clause on modules.
Let’s first check with Ownership Chaining and then go for Impersonation. I did this in a TestDatabase, you can do the same. The first code snippet creates two logins and two users. Note that SalesPerson is added to db_owner role.
With next code you can see that Impersonation has been done explicitly using EXECUTE AS. What I do here is, impersonate SalesPerson and then create a schema and a table. Note that SalesPerson becomes the owner of these objects.
Next code impersonates MarketingPerson and sees whether he can access the table Sales.Sales. Since he has no permission on this table, error is thrown for SELECT query.
“The SELECT permission was denied on the object 'Sales', database 'TestDatabase', schema 'Sales'.”
Let’s check Ownership Chaining. With next code, SalesPerson creates a stored procedure that returns data from Sales.Sales, and he grants EXECUTE permission to MarketingPerson.
We know that MarketingPerson has no read permission on Sales.Sales table but with next code, you can see that he can read the table through Sales.GetSales procedure.
The reason for this is, the Ownership Chain. As MarketingPerson has EXECUTE permission, he can execute the SP. Although he has no permission on Sales.Sales, SQL Server does not evaluate permission on it for MarketingPerson because it is owned by the same owner who owns Sales.GetSales. Note that we have not explicitly set owners for both Sales.GetSales and Sales.Sales, hence owners of them are considered as Sales schema’s owner; SalesPerson. Below code shows it;
If the SP contains an object owned by someone else, it breaks Ownership Chain. Let’s test this too. Below code adds a new table, note that no impersonation is established, so, it is created by dbo. Since it is under Sales schema too, the owner of the schema (SalesPerson) becomes the owner of the new table.
Next, the code of the SP is altered by SalesPerson. Now it contains both tables but still MarketingPerson can execute the SP without any issues even though he has no permission on new table.
Still the Ownership Chain works. Let’s break it. Let’s change the owner of the new table Sales.SalesCommisions as dbo.
Now if MarketingPerson executes the SP, he gets an error for Sales.SalesCommisions table:
”The SELECT permission was denied on the object 'SalesCommisions', database 'TestDatabase', schema 'Sales'.”
The reason is, the second object, Sales.SaleCommisions is not owned by the same person who owns the SP, hence SQL Server checks and sees whether MarketingPerson has permission on Sales.SaleCommissions.
How can we let MarketingPerson to execute the SP without giving explicit permission on Sales.SaleCommissions? Impersonation is the answer. We can use EXECUTE AS explicitly with the stored procedure, instructing SQL Server to execute the stored procedure with someone else privileges without using CALLER’s privileges. See the code below;
Now if MarketingPerson executes the SP, he will see the result without seeing an error because the execution context for the SP’s execution is set with SalesPerson not with MarketingPerson.