What is the significance of the principal_id in sys.schemas?

Question

What is the meaning of the principal_id in sys.schemas and when would it ever be different from schema_id?

1> SELECT LEFT(name,20), schema_id, principal_id FROM sys.schemas;
2> GO
                     schema_id   principal_id
-------------------- ----------- ------------
dbo                            1            1
guest                          2            2
INFORMATION_SCHEMA             3            3
sys                            4            4
db_owner                   16384        16384
db_accessadmin             16385        16385
db_securityadmin           16386        16386
db_ddladmin                16387        16387
db_backupoperator          16389        16389
db_datareader              16390        16390
db_datawriter              16391        16391
db_denydatareader          16392        16392
db_denydatawriter          16393        16393

(13 rows affected)

Internally, I see that the schema_id comes from sys.sysclsobjs while the principal_id comes from sys.syssingleobjrefs's r.indepid field.

Answer 1

It should be the default "owner" of all objects in the schema. Schema-bound objects have a principal_id column that is NULL by default, in which case it uses the principal_id of the Schema that the object is in.

The object owner is used for ownership chaining, and most likely also when determining if rights exist to perform operations that can be done by the object owner or a User in a the db_owner Database Role, such as TRUNCATE TABLE, SET IDENTITY_INSERT, etc.

The principal_id in sys.schemas can be any User if changed via ALTER AUTHORIZATION.

If using Schemas for logical separation of objects more than security separation, then it would make sense to use the same principal_id across multiple Schemas. For example, executing the following in the AdventureWorks2012 Database:

SELECT * FROM sys.schemas;

shows that the following Schemas are all owned by "dbo":

dbo
HumanResources
Person
Production
Purchasing
Sales

Having the same "owner" across Sales and Purchasing (and the others) allows ownership chaining to imply permissions of referenced objects within top-level objects. Meaning, if you execute a Stored Procedure in the Sales Schema, and it selects from a View in the Purchasing Schema, then permissions won't be re-checked and everything just works. But, if you then specify the principal_id of the View in the Purchasing Schema such that the column is no longer NULL (and not the same as the "dbo" User) then it will re-check permissions to see if the User executing the Stored Procedure has SELECT permissions on that View.