Grant Access to user for using any schema objects with denying for direct using objects from other schemas

Question

I have 2 schemas first and second. Can I grant access for a user to objects in first schema that uses object from schema 'second' inside without granting direct access to schema 'second'?

Example: Function in schema first

CREATE FUNCTION first.IsIdExists
(
    @Id int
)
RETURNS bit
AS
BEGIN
    IF (EXISTS (SELECT * FROM second.GetIds AS i WHERE i.Id = @Id))
    BEGIN
        RETURN
    END
END

second.GetIds - Table in schema second

If I grant access just to schema first, I get

The SELECT permission was denied on the object 'GetIds', database 'testDb', schema 'second'.

Answer 1

Can I grant access for a user to objects in first schema that uses object from schema 'second' inside without granting direct access to schema 'second'?

Users don't need permissions on indirectly referenced objects as long as all the objects involved are owned by the same user (ownership chaining). Only permission on the objects directly referenced by the query are required.

Since object ownership is typically inherited from the schema owner, it seems the first and second schemas are owned by different users, resulting in a broken ownership chain. Both first and second schemas need to be owned by the same user for an unbroken ownership chain. For example:

USE YourDatabase;
CREATE USER CommonOwner WITHOUT LOGIN;
ALTER AUTHORIZATION ON SCHEMA::first TO CommonOwner;
ALTER AUTHORIZATION ON SCHEMA::second TO CommonOwner;
GO

Be aware of the security implications if these schemas are intended to be a security boundary for non db_owner users. Users with permissions to create objects in the first schema, but no permissions on schema second objects, could elevate their data access to schema second data by creating a proc with an unbroken chain.