SQL Server Blog

When SQL Server Database Owners Are NULL or Mismatched

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

Let’s cover the basics:

In SQL Server there are two groups that encompass the domain of security and reliability that we will be covering in this blog post.

Principals: which are individuals, groups, or processes than have been granted access to the SQL Server Instance. They include logins, users, and roles. On the other hand

Securables represent protected resources, such as databases and objects within them. Each securable has associated permissions that control access.

This can be illustrated in many ways; I will use the idea of an airport for the examples in this post. There is public access for anyone who is in the country (principals). Certain principals have different forms of ID and reasons for being there (Permissions) and based on those permissions can access different restricted areas (Securables) including terminals and the planes.

Securable ownership:

The owner of any securable, be it a database, schema, or even an individual table, possesses absolute control over it. This means that the login that owns it has the authority to determine how it is accessed, manipulated, and used. No privilege related to that securable can be denied to the owner. This level of control underscores the importance of careful assignment of ownership. As DBAs, we must ensure that securable ownership is granted judiciously, keeping in mind the principle of least privilege.

Now on to the Topic at hand: Database Ownership –

Using the concepts above as a foundation we typically recommend that users that change with any frequency should never own a SQL Server Securable. By default, whatever login creates a securable will be the owner. When we run our security and health checks we typically identify this issue where a user who could leave or get their account locked as the owner of databases and jobs. Our recommendation is to, unless it falls into a few specific categories such as SSRS jobs and legacy apps, to make the owner an account that will not cause an issue for reliability down the line. In general, we have recommended that an sa account that has connection disabled is a satisfactory solution. However, if there is any chance that your databases could be set to trustworthy then you should probably read Andreas Wolter’s post on this issue: SQL Server Database Ownership: survey results & recommendations (insidesql.org). To summarize though, if the owner is sa and the database is set to trustworthy, your instance is open to privilege escalation. The resolution for this is to create a low privileged account to own things instead, again Andreas gets into this deeper if you are interested in that path.

A couple of other issues we end up seeing during our checks is that databases have no owner, or a mismatch of owners between master and the database itself. The root of both is related to restoring the databases onto a different instance. Either the owner login from the previous server does not exist on the new server, which leaves the owner as null on the new server, or the login does exist but the database was restored or ‘created’ by a different user, causing the mismatch. The script below can help you identify all database owners, as well as provide the script to resolve it if so desired. Please be sure that you fully understand the possible damage that could be caused by changing the ownership of a database and test in a sandbox environment first.  I have left the new owner field open for you and your team to decide what is best for your environment.

/*~~~ DB owner~~~*/
    SELECT @@SERVERNAME as [Server Name]
    ,   name as [Database Name]
    ,   suser_sname(s.owner_sid) as [Current Owner]
    ,   CASE WHEN IS_SRVROLEMEMBER('sysadmin', suser_sname(s.owner_sid)) = 1 Then 'Yes'
        ELSE 'No' END AS [Is Sysadmin?]
    ,   'USE ' + QUOTENAME(s.name) + ';
        EXEC sp_changedbowner ''<New Owner Name>'';
        CREATE USER [' + suser_sname(s.owner_sid) + '] FOR LOGIN [' + suser_sname(s.owner_sid) + ']
        ALTER ROLE [db_owner] ADD MEMBER [' + suser_sname(s.owner_sid) + ']
        GO' as [Script when is NOT sysadmin]
    ,   'USE ' + QUOTENAME(s.name) + ';
        EXEC sp_changedbowner ''<New Owner Name>''; /* Previous owner = ' + suser_sname(owner_sid) + '*/
        GO' as [Script when owner IS sysadmin]

        FROM sys.databases AS s
        WHERE s.owner_sid <> 0x01 AND s.state_desc <> 'OFFLINE'
        order by name
David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This