Who is the Default Owner of your Database and Server Objects?

2014-03-04 - General, Security

Introduction

Every securable in SQL Server has an owner. Well, almost every securable: Logins and users cannot have a user specified, but you could consider them as being owned by themselves.

Today I would like to look at, what happens if you do not explicitly specify an owner during or after the creation of a securable.

Creating a Schema with Default Owner

Before we can dive right into creating schemata, we first need a few principals. For that, we can use the following SQL batch:

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;
CREATE LOGIN TestLogin3 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE USER TestUser3 FROM LOGIN TestLogin3;
CREATE LOGIN TestLogin4 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE LOGIN TestLogin5 WITH PASSWORD='********',CHECK_POLICY=OFF;

There are five logins and three users created. You will see later why two of the logins did not get a user associated with them.

Now let us use TestLogin1 to create a schema:

GRANT ALTER ANY SCHEMA TO TestUser1;
GO
EXECUTE AS LOGIN='TestLogin1';
GO
  CREATE SCHEMA TestSchema1;
GO
REVERT

The GRANT statement grants the required permissions to create a schema in the database. The EXECUTE AS than switches the security context to TestLogin1 before creating the schema TestSchema1.

The next step then is to check which principal ended up being the owner of that newly created schema. We can use this query for that:

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

Not totally unexpected, the executing principal ended up being the owner of the new schema:

Default Owner for a SCHEMA created by a user with ALTER ANY SCHEMA permission.

To be exact, the owner is not TestLogin1 but the associated user in the database, TestUser1.

It makes sense for the creator to also be the owner of a securable. However, when you look at a real life system, most database securables are owned by dbo. Let us try to figure out why that happens.

Maybe granting CONTROL on the database to the creator will change this behavior? Let's try:

Default Owner for a SCHEMA created by a user with CONTROL DATABASE permission.

No luck there, the creator is still the owner. dbo stands for "database owner", so maybe adding the creator to the db_owner role will change the behavior?

Default Owner for a SCHEMA created by a member of the db_owner fixed database role.

Again, no luck. But the actual database owner should work.

Default Owner for a SCHEMA created by the actual database owner.

It did, or did it? dbo is actually a standard user in the database and it has an associated login. That login is, you guessed it, the database owner:

The dbo user is associated with the login the owns the database.

So while securables created by the database owner actually are owned by dbo, this is still the user that created them. Also, most objects are probably not created by the database owner itself, so this still does not explain why most securables end up with dbo as owner. Let us go one step further and look at members of the sysadmin fixed server role:

Default Owner for a SCHEMA created by a member of the sysadmin fixed server role.

And here we have a good explanation. Most members of the sysadmin fixed server role do not have an associated user in each database. However, the owner of a securable with database scope has to be a user, so it makes sense for SQL Server to default the owner to dbo, a user that always exists, when such a securable is created by a sysadmin. Taking into account, that most create scripts are probably executed by a sysadmin, you would expect to find dbo being the most prevalent owner.

The Default Owner for Server Scope Securables

There is no dbo concept for server scope securables. They are always owned by the login that created them, no matter of any server roles that the login might be a member of. That behavior can lead to problems if the owner is a windows login, particularly if the newly created securable is a database. It is advisable to always change the owner of a server scope securable to a SQL Login like SA. This is possible even if SQL authentication is disabled on the instance.

Summary

In most circumstances, the owner of a newly created securable is the principal executing the create statement (or their associated user in the database). However, if a member of the sysadmin fixed server role creates a database scope securable like a schema, the owner is set to dbo.

Categories: General, Security
Tags: , , , , , , , ,

2 Responses to Who is the Default Owner of your Database and Server Objects?

  1. KudZ says:

    Hi there , may I ask you who is the owner of the dbo schema ? In my server the owner is sa and therefore I couldn’t have ownership chaining working because the owner of the DB was different than the owner of the dbo schema. Is this correct? So changing the db owner will change the dbo user but the dbo schema still belongs to a different user…

  2. @sqlity says:

    KudZ The dbo schema is owned by dbo user. (https://msdn.microsoft.com/en-us/library/bb669065(v=vs.110).aspx)
    The dbo user is linked to the actual database owner, but it is also impersonated every time a sysadmin accesses the database.

Leave a Reply