Home » Blog » Schema Owner vs. Object Owner – Who wins?

Schema Owner vs. Object Owner – Who wins?

2014-03-02 - General, Security

Introduction

In SQL Server, by default, a securable that is part of a schema is owned by the owner of the schema. However, you can change the securable to be owned by a different principal. We know that any permission granted on the schema also applies to all securables that are part of that schema, at least when they are owned by the same principal. With that in mind you might ask, if that rule is still true for different owners. Let us investigate

Example of an object not owned by the schema owner

To start out we need two principals:

[sql] CREATE LOGIN TestLogin1 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE USER TestUser1 FROM LOGIN TestLogin1;
CREATE LOGIN TestLogin2 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE USER TestUser2 FROM LOGIN TestLogin2;
[/sql]

The next step is to create a schema, owned by one principal, and a table within that schema, owned by the other principal:

[sql] CREATE SCHEMA TestSchema1 AUTHORIZATION TestUser1;
GO
CREATE TABLE TestSchema1.tst(id INT);
ALTER AUTHORIZATION ON OBJECT::TestSchema1.tst TO TestUser2;
[/sql]

Remember, while some securables allow the owner to be specified during creation as in the CREATE SCHEMA statement above, others, like a table require a separate change-the-owner statement.

To see that the assignment of ownership worked as expected, we can use this query:

[sql] SELECT S.name AS schema_name,USER_NAME(S.principal_id) AS owner_name
FROM sys.schemas AS S
WHERE S.name = 'TestSchema1';

SELECT T.name AS table_name,
USER_NAME(T.principal_id) AS owner_name,
SCHEMA_NAME(T.schema_id) AS schema_name
FROM sys.tables AS T
WHERE T.name = 'tst';
[/sql]

It produces the following output:

Know let us look at the effects of the ownership setup on the permissions. We know that TestUser2, owning the table, should have unrestricted access to it. However, to be sure, let us confirm that this is indeed the case by running the following query:

[sql] EXECUTE AS USER='TestUser2';
GO
SELECT * FROM sys.fn_my_permissions('TestSchema1.tst','OBJECT') AS FMP;
GO
REVERT;
[/sql]

It produces this output:

As you can see, the CONTROL privilege is part of the active permission set for TestUser2 which translates into unrestricted access.

The big question now is, if TestUser1 (as owner of TestSchema1) still has unrestricted access to the table, even though the table itself is owned by TestUser2. Let us try by running the above query again, but this time for TestUser1:

As before, the CONTROL permission is returned. That proves that the permission transfer along the hierarchy of securables is independent of the owner of those securables. Just to clarify, this is true for other principals that have been granted a privilege on the schema too, not only for the owner of the schema.

So, to answer the question in the title: Neither wins, both can happily coexist.

Summary

A Schema and a table that is part of that schema have two different owners. However, a permission granted on the schema still automatically applies to each contained table (or any other securable) as well, independent of them having different owners. In particular, the owner of the schema has always unrestricted access to every securable that is part of that schema.