Who’s the SQL Server Database Owner and How Can You Change It?

In SQL Server, when someone creates a database, they own it. That means they have elevated permissions on the database. The SQL Server database owner can change configuration parameters, perform maintenance, and grant permissions on the database to other users. The database owner can even drop the database altogether.

In highly secure environments (and what environment shouldn’t be classified as highly secure these days?) all of these permissions can create headaches for auditors, CISOs, and even DBAs.

That’s not good. Yet, it’s not uncommon for individual users to own specific and important databases. In fact, this is one of the common findings we uncover during our SQL Health Checks for customers.

Who Owns Your SQL Server Database?

So how do individual users end up becoming SQL Server database owners?

Take, for example, the Chief Marketing Officer who authorizes her team to build a database to track their activities. Over time, the database grows and becomes more important. Eventually the team doesn’t want to “own” the responsibility for it any longer so it’s handed over to the DBA team for updates, maintenance, backups, HA/DR planning, licensing, etc. Except, no one really thought about the database owner during the transfer. So the power user that created the database still owns it, even though he doesn’t work on the CMO’s team any longer.

So, how can you tell if this may have happened in your environment? Or put another way, how can you tell who the database owner is for a database? Let’s look at three ways.

Find the database owner using Database Properties

One of the easiest ways to determine the database owner is to view its properties. Using either SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) drill down to the database, right click on it, and select Properties to open the following window. Under the Database heading, you’ll see its owner.

In this example, the BaseballData database is owned by the Joe login.

Pretty simple for a single database, but very cumbersome for a few dozen or a few hundred databases spread across dozens of SQL Servers. So let’s look at two programmatic ways to check the database owner.

Find the database owner using T-SQL

Using your SQL Server query tool of choice, SSMS or ADS, run the following T-SQL query to see the owner for every database attached to this instance of SQL Server.

 SELECT name, 
        suser_sname( owner_sid ) AS DBOwnerName
 FROM master.sys.databases;

Or, if you prefer, you can limit the results to show only those databases not owned by a specific user, such as sa.

 SELECT name, 
        suser_sname( owner_sid ) AS DBOwnerName
 FROM master.sys.databases
 WHERE suser_sname( owner_sid ) != 'sa';

In my example, I’d get the following results in an ADS window.

Find the database owner using PowerShell and dbatools.io

For fans of PowerShell and dbatools, the Get-DbaDatabase command can be used to determine the database owner. In the code below I pipe the results to a Where-Object to show only those databases not owned by sa. For readability, I pass the results through Format-Table.

Get-DbaDatabase -SqlInstance localhost -SqlCredential sa | Where-Object {$_.Owner -ne "sa"} | Format-Table -Property Name, Owner 

Who Should Own Your Database

Now that you know who your SQL Server database owners are, the next question is: who should own your databases?

There are differing thoughts on this. Some advocate creating a specific disabled account to own all databases. Others contend that sa should own the databases. Still others opt for other approaches.

We recommend having sa own all databases. It’s not a perfect solution, but it’s consistent, clear, and meets the company and compliance needs in most cases.

How to Change Who Owns Your Database

Once you’ve decided who the SQL Server database owner should be and you’ve identified those databases not owned by that account, let’s look at a couple of ways you can change to database owner.

Change the database owner using T-SQL

To change database owner, use the ALTER AUTHORIZATION command. In the following example, I’m changing the owner of the BaseballData database to sa. If needed, you can script this out for every database on the server that isn’t already owned by sa.

ALTER AUTHORIZATION ON DATABASE::BaseballData TO sa;

Change the database owner using PowerShell and dbatools.io

You can also use the Set-DbaDbOwner command of dbatools.io to do this. In the following command, I’m setting the owner of the BaseballData database to the command default sa.

Set-DbaDbOwner -SqlInstance localhost -SqlCredential sa -Database BaseballData

If I don’t want to use the sa account as the database owner, I can specify who the owner should be using the TargetLogin parameter as shown below.

Set-DbaDbOwner -SqlInstance localhost -SqlCredential sa -Database BaseballData -TargetLogin disabled_login_with_complex_pwd

Or, I can broaden the scope to include all databases on the specified instance by omitting the Database parameter, as shown below.

Set-DbaDbOwner -SqlInstance localhost -SqlCredential sa 

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.

Download our free “SQL Server Configuration: 5 Common Issues That May Be Putting Your Data at Risk” PDF and discover some commonly overlooked SQL Server settings that can have dramatic effects on your server’s performance and reliability.