Ownership chaining

When a user executes a stored procedure, does permission for each object (table, view or function) called inside the stored procedure is checked?

Answer is No. SQL Server uses a concept called Ownership Chaining to decide whether permission for an referenced object would be checked or not. Permission is checked whenever ownership chaining breaks. Let’s understand it with an example.

Here we take three users: UserA, UserB and UserC

UserA owns a table tblUserA

UserB owns a table tblUserB

UserA grants SELECT permission on table tblUserA to UserB.

UserB create a stored procedure usp_UserB in which both tables are used.

UserB grant EXECUTE permission on stored procedure to UserC.

Now take different execution scenario and status:

SP statements	UserB execute		UserC execute
	Permission check	Statement result	Permission check	Statement result
Execute usp_UserB	No	Success	Yes	Success
SELECT * FROM tblUserB	No	Success	No	Success
SELECT * FROM tblUserA	Yes	Success	Yes	Failed
Stored procedure execution status		Successfully completed		Failed

From the above result we can conclude that permission is checked at every statement where owner of called object changes. If all referenced objects are owned by owner of stored procedure or view then this ownership scenario is called unbroken ownership chaining. But if owner of any referenced object changes then its called broken ownership chaining.

The script to verify the above explanation is as below:

CREATE LOGIN UserA WITH PASSWORD = 'usera', CHECK_POLICY=OFF 

CREATE LOGIN UserB WITH PASSWORD = 'userb', CHECK_POLICY=OFF

CREATE LOGIN UserC WITH PASSWORD = 'userc', CHECK_POLICY=OFF

GO

USE Test

GO

CREATE USER UserA

CREATE USER UserB

CREATE USER UserC

GO

sp_addrolemember 'db_ddladmin', N'UserA'

GO

sp_addrolemember 'db_ddladmin', 'UserB'

GO

sp_addrolemember 'db_ddladmin', 'UserC'

GO

CREATE SCHEMA SchemaA AUTHORIZATION UserA

GO

CREATE SCHEMA SchemaB AUTHORIZATION UserB

GO

CREATE SCHEMA SchemaC AUTHORIZATION UserC

GO

EXECUTE AS USER = 'UserA' 

GO

CREATE TABLE SchemaA.tblUserA (col1 INT)

GO

GRANT SELECT ON SchemaA.tblUserA TO UserB

GO

REVERT

GO

EXECUTE AS USER = 'UserB' 

GO

CREATE TABLE SchemaB.tblUserB (col1 INT)

GO

CREATE PROC SchemaB.usp_UserB 

AS

SELECT * FROM SchemaB.tblUserB

SELECT * FROM SchemaA.tblUserA

GO

GRANT EXECUTE ON SchemaB.usp_UserB TO UserC

GO

EXEC SchemaB.usp_UserB            -- completes successfully

GO

REVERT

GO

EXECUTE AS LOGIN = 'UserC' 

GO

EXEC SchemaB.usp_UserB    -- Failing with error: The SELECT 

-- permission was denied on the 

-- object 'tblUserA', schema 'SchemaA'.

If for testing you have executed the above script in your database then to undo all changes execute the below script:

REVERT

GO

REVERT

GO

drop table SchemaA.tblUserA

drop table SchemaB.tblUserB

DROP PROC SchemaB.usp_UserB

DROP SCHEMA SchemaA

DROP SCHEMA SchemaB

DROP SCHEMA SchemaC

GO

drop user  UserA

drop USER UserB

drop USER UserC

GO

drop LOGIN UserA

drop LOGIN UserB

drop LOGIN UserC