Home » Blog » The Mysterious Schema Owner

The Mysterious Schema Owner

2014-03-01 - General, Security

Introduction

Yesterday we looked at the concept of securable owners. Every securable in SQL Server has a single owner, for example a user or a database role. The owner automatically has unrestricted access to the securable, even without any implicit grants.

Today I would like to invite you to look in a little more detail at the ownership concept for securables that belong to a schema.

Schema Owner Example

As always, to look at an example we first need a few principals in place:

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

We also need a schema:

[sql] CREATE SCHEMA TestSchema1 AUTHORIZATION TestUser1;
[/sql]

Many securables allow us to specify an owner right in the create statement by using the AUTHORIZATION clause as in the example above.

However, there are also quite a few securable classes that do not have that option. For those securables, you have to use a separate ALTER AUTHORIZATION statement to change the owner after the fact.

One example of a securable that does not allow to specify the owner in the create statement is the table. Let us look at what owner is selected when we create one.

[sql] CREATE TABLE TestSchema1.tst(id INT);
GO
SELECT T.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]

The select statement returns the table name, the name of the owner and the name of the schema of our newly created table. It produces this output:

But hold on, it seems that there is no owner set at all. This should not be possible. Let us quickly re-confirm that tables actually can have an owner:

OK, so when we explicitly change the owner of a table it shows up in sys.tables. But after creation there is no owner set?

Well, there actually is an owner set. In the case of securables that live inside a schema like database objects or types a special rule applies. Upon creation, the securable is automatically owned by the owner of the schema. That is indicated by the fact that there is no explicit owner set after creation of such a securable and the respective catalog view returns principal_id = NULL.

To default the schema owner as owner of all contained objects makes a lot of sense from a security management perspective. It for example makes for more predictable ownership chaining, a concept I will cover in a later post.

If you have changed the owner of an object and would like to set it back to be owned by the schema owner, you can use the ALTER AUTHORIZATION clause like this:

[sql] ALTER AUTHORIZATION ON OBJECT::TestSchema1.tst TO SCHEMA OWNER;
[/sql]

It sets the principal_id back to NULL, indicating that the table is now owned by the schema owner again:

The same syntax works with other securables that live in a schema too.

Summary

Securables that live inside a schema, like tables or types, are by default owned by the owner of the schema itself. This is indicated by a NULL in the principal_id column of the respective catalog view.