If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema? Does my new schema inherit the same permissions from it's owner dbo?
asked Sep 10, 2019 at 21:18 38.6k 10 10 gold badges 59 59 silver badges 128 128 bronze badgesA Schema's owner is always a User (or Database Role). There may be a Schema with the same name as a User, because in very old versions of SQL Server a User and a Schema were the same thing. So there's still a User called dbo and a Schema called dbo in every database.
A Schema's owner is a User who has full control of the schema, and who will be the default owner of every object added to the schema. Owning an object suppresses permissions-checking on the object, and plays an important role in Ownership Chains.
answered Sep 10, 2019 at 21:29 David Browne - Microsoft David Browne - Microsoft 47.3k 3 3 gold badges 50 50 silver badges 99 99 bronze badgesAlthough rarely used in my experience, one can change the owner on individual objects with ALTER AUTORIZATION ON OBJECT::SomeObject TO UserWhoIsNotSchemaOwner such that the object owner is different than the schema owner. The schema owner will not be inherited in that case.
Commented Sep 11, 2019 at 11:03@DanGuzman could you please elaborate on "such that the object owner is different than the schema owner" a little bit? I just stumbled on using the ALTER AUTHORIZATION ON SCHEMA::SchemaName TO User and then when I view the properties on that schema, it now shows the User as the Schema Owner.
Commented Sep 11, 2019 at 12:30You can issue ALTER AUTHORIZATION ON SomeSchema.SomeTable to switch the owner of a single object. It then no longer inherits ownership from the schema. But as @DanGuzman this functionality is so obscure that it's actually dangerous, given the serious security implications of Ownership Chains.