What is an ownership chaining?
Each object within a database has an owner associated with it i.e. schema owner. You can also build objects that reference other objects within a database, such as SPs that call functions which issue SELECT statements against the views that are based on the tables. The owner of the each object that is referenced in a calling stack forms an ownership chain as the code transits from one object to the next within the calling stack. So long as the owner of the object and any other objects that it references have the same owner , you have an intact ownership chain.SQL SERVER checks your permissions on an object at the top of the calling stack , as well as each time the object owner changes within a calling stack.
Test scenario:
Consider the table Testtable contained in the schema called Mainschema which is owned by a user ‘mainuser’. we can create a view named vw_test in another schema called ‘interschema’ with another user called ‘interuser’ . we are giving acess to the application using an SP who is contained in another schema called ‘appschema’ and user owns the schema is ‘appuser’ . The SP is selecting the data from the vw_test.
The steps are as follows,
- Create three users without login and add role db_owner to user Mainuser
- Create three schemas and authorize the users to access the schema.
- The below script shows the schemas and their owners
.
-
Create a view vw_test under theschema“interschema”. We also granting acess to the Mainschema.Testtable to interuser.
Ownership chain works in the above script.The appuser have only acess to the SP not to the undelying tables or views.This is a great security feature in sql server which is introduced in SQL SERVER 2005.I have done these test scenarios in the SQL SERVER 2008.Hope every one likes this article.