Can a database on SQL Server be owned by no one?

Yes, a database on SQL Server can be owned by no one! This is a NULL value as the owner. Apparently, this can happen because the login that owned it no longer exists.

I discovered this when I was going through databases owned by users who left the company. It seems like this doesn’t cause any issues (that I know of), but I went ahead and changed the owner.

This made me think about who should really own a database. It definitely shouldn’t be just whoever created it. This tends to happen a lot because you need a new database, and when you create it, you are the owner if you don’t set it to another user as part of your process.

In order to see who owns which databases, you can run this script:

select suser_sname(owner_sid) as username, name
from sys.databases

The script will return the name of the database and who owns it. I have a registered server list already set up (here’s a good article on setting up and using registered servers) so I can run this against all of my SQL Servers at once. I even caught myself as the owner on some of the databases, and some are in production, and some are even in an availability group (ugh because fixing database owners in an AG secondary is a whole other story, which requires failing over to the secondary to update the owner and then failing back to the primary if needed). And I have some other naughty users who own databases in production, so I will want to fix those, as well.

We need to be careful about who we chose to be the owner of a database because this person gets superpowers over this database and could potentially wreak havoc.

You could choose a DBA to be the owner of the database, but if that person leaves and their account is disabled, it could cause issues (not really likely, but it’s best to avoid this). You might think it would be a good idea to set the owner to a group of DBAs, but this isn’t allowed. An AD group can’t own a database.

A lot of times we make sa the owner on all the databases because it has SQL Server level superpowers anyway. It’s recommended to disable the sa account for security reasons like preventing an attack with the default admin account like sa. Attackers will know that sa exists and will try a brute-force attack on this account to gain sysadmin level access to your SQL Server. This is especially important for anything that may be public-facing. Hopefully, you don’t have anything like that, but I’ve seen some Azure VMs with SQL Server setup so that people can hit it from the outside world and lots of random people try to hack sa.

But, it’s good to have a sysadmin account that is usable, so you can create another SQL user that has sysadmin permissions. The following example will take you through creating a user and using that, but you can also use the disabled sa account instead if you want.

You can create the new user with the following code (changing out newuser to the name of your choice):

USE [master]
GO
CREATE LOGIN [newuser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [newuser]
GO

Now, that you have your new users with sysadmin rights in place, you can make it the owner of your databases (except system database, they have to remain owned by sa):

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [newuser]

Let’s say you want to update the owner on many database as the same time. You can run a query that gives you the queries you need to run to update the database owner. I ran the following query against my registered server list (or you could run it against individual servers):

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [newuser];'
from sys.databases
where name not in ('master', 'model', 'tempdb', 'msdb')
AND suser_sname(owner_sid) <> 'newuser'

The previous query will give you zero or more rows providing you with a query that you can execute to update the database owner. In this case, I had two databases to update, but your’s will vary based on the number of databases to update. The following code shows you what you can expect to copy out of the results of the previous query:

ALTER AUTHORIZATION ON DATABASE::[dbname1] TO [newuser];
ALTER AUTHORIZATION ON DATABASE::[dbname2] TO [newuser];

Then you just run those ALTER AUTHORIZATION queries and your databases are now owned by a new user!