Your SQL Server Database Owner Might be Causing Privilege Escalation

SQL Server database ownership may seem like an insignificant concern, but choosing the wrong owner for your database can be a main contributor to security disasters like ransomware. Let’s talk a bit about how to choose an owner that doesn’t create a huge security vulnerability for you and your SQL Server instance.

(Note: this isn’t the same as being in the db_owner role, although we will look at that later in this post.)

Who is the current owner of your databases?

You can see who owns your database with this simple query.

SELECT
    [name] AS DatabaseName
    , SUSER_SNAME(owner_sid) AS DatabaseOwner
FROM sys.databases
WHERE database_id > 4

When a database is created, the owner defaults to the principal who created it. Many folks may not realize this, but the owner of the database gets additional permissions within that database such as, well, the ability to do anything in that database. Create objects, change permissions, drop the database – you name it. However, this usually doesn’t result in noticeable issues because the owner of the database is often a member of the sysadmin role, so they already have the highest possible permissions inside all databases on the SQL Server instance.

If the owner is ever changed, it is often changed to the sa login. Many folks change the owner to the sa login because it’s the easiest way to avoid problems if the person who created the database leaves the organization and has their permissions removed, or if they have to failover to another instance, since the sa is on every instance by default.

As noted elsewhere, the sa login is also in the sysadmin role, and therein lies the security issue.

What’s the problem with these owners?

The problem here is that, with a member of sysadmin role (the sa login or some other principal) owning the database, there is now an opportunity for privilege escalation by other principals that is possible if there are other features enabled.

If you have Cross Database Ownership Chaining enabled at the instance level, or even Database Chaining enabled in any of your user databases, this can lead to an escalation of privileges related to creating database objects in other databases. And if you have enabled the TRUSTWORTHY setting on a database that is owned by a member of the sysadmin role, you have basically given the option for any member of the db_owner role in that database to execute scripts as a sysadmin.

That’s basically the pinnacle of privilege escalation inside of SQL Server, as it allows for code to be executed externally through features like xp_cmdshell and OLA Automation objects.

How do we fix this problem?

If you want to read a more detailed post than this, I’d recommend reading this post on SQL Server Database Ownership from Andreas Wolter, former Microsoft Senior Program Manager in charge of SQL Server Security. The post is over a decade old, but much of it still holds true. As Andreas noted, there is no simple way to define best practice when it comes to the database owner. But I can tell you, there are some simple steps you can take to avoid a security disaster.

First, see if you can create a new SQL Server login that has low permissions, like just being in the public role. We refer to this as your “preferred” database owner in sp_CheckSecurity. Being an owner of a database has enough elevated permissions, so we don’t need to grant this login any further permissions than needed.

Next, create this owner on all your instances that might have these databases (think of non-production instances where you restore copies for testing and other fun things), and make sure you create the new login with the same SID. Andy Mallon has written a helpful post about copying logins effectively.

Also, make sure the login is disabled on all instances. Disabling it doesn’t prevent SQL Server from using the principal, but rather it only prevents it from being used to connect to the instance. We just want to prevent any actual people from using this database owner.

Finally, test assigning ownership of this database to the new login, making sure nothing breaks. You probably won’t have an issue by changing the owner, but it is possible if you have any T-SQL that uses “EXECUTE AS OWNER…”

Yes, there is more that you could do, such as creating multiple owners that are specific to groups of database, which can give you an even greater degree of control of privileges. The main thing is to start removing those sysadmins as databases owners – especially on any databases that have TRUSTWORTHY enabled.

Here, let’s expand the previous query to show if you have any databases with the TRUSTWORTHY setting enabled.

SELECT
    [name] AS DatabaseName
    , SUSER_SNAME(owner_sid) AS DatabaseOwner
    , CASE is_trustworthy_on
        WHEN 1 THEN 'YES'
        ELSE 'NO' END AS IsTrustworthyOn
FROM sys.databases
WHERE database_id > 4;

And while you’re at it, take a look at who is in the db_owner role in your databases – especially any databases that have TRUSTWORTHY enabled, since they can use “EXECUTE AS OWNER…” to potentially escalate their privileges…but then again, this is why you are going to make all these changes in database ownership.