When SQL Server Database Owners Are NULL or Mismatched

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) + ';
        GO
        EXEC sp_changedbowner ''<New Owner Name>'';
        GO
        CREATE USER [' + suser_sname(s.owner_sid) + '] FOR LOGIN [' + suser_sname(s.owner_sid) + ']
        GO
        ALTER ROLE [db_owner] ADD MEMBER [' + suser_sname(s.owner_sid) + ']
        GO' as [Script when is NOT sysadmin]
    ,   'USE ' + QUOTENAME(s.name) + ';
        GO
        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