SQL security & Ownership chaining

In SQL server, every DB object belongs to one grouping, called schema. Schemas are required to have owners, while it’s optional for underlying objects. If no owner is set to a schema (set by AUTHORIZATION keyword in Create Schema command), then the user creating the schema will be the owner. If no owner is set to an object, then the owner of the objects’s schema has owner rights to that object. Users can be grouped into roles.

We can see the schemas in a database and the owners of those schemas using the following query:

SELECT [name] AS [schema]
, [schema_id] , USER_NAME(principal_id) [Owner]  FROM sys.schemas;

If a user u1 is granted access to an object o1 which (view or SP) references another object o2, and both o1 and o2 are have same owner, u1 will be automatically given the amount of access to o2 that is specified in o1. This is called ownership chaining. This can help limiting viewing and modifying data to a certain way, like permission deleting one row at a time or viewing a subset of data (rows and columns).

Cross database chaining needs to be enabled to allow objects in one database to reference other dayabases. This can be useful in Enterprise DW when EDW references multiple data mart DBs, in granting DW access to datamart users and limiting them to their data.


Leave a Reply

Your email address will not be published. Required fields are marked *