3

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.

4

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.

  • ahh, so schemas are users under the hood? that's cute. – Evan Carroll Dec 22 '17 at 3:59
  • 4
    No, I am not saying that. Pre SQL Server 2005 there were no Schemas, and so the blah.ObjectName was "owner" == "blah". but starting in 2005 they added Schemas which are containers of objects. Those containers are associated with Users, but are not Users per se. – Solomon Rutzky Dec 22 '17 at 4:03
  • Look at the linked page for ALTER AUTHORIZATION. I believe it discusses that. – Solomon Rutzky Dec 22 '17 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.