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

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:

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:

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:

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

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:

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?

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

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:

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:

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.