Why is database ownership by groups not allowed? Which user should own a database?

Question

Per microsoft "ALTER AUTHORIZATION for databases for SQL Server":

Requirements for the new owner:

The new owner principal must be one of the following:

• A SQL Server authentication login.
• A Windows authentication login representing a Windows user (not a group).
• A Windows user that authenticates through a Windows authentication login representing a Windows group.

Apparently a group cannot own a database. Indeed invoking

alter authorization on database::MyDatabase to [domainname\SQLServerAdminGroup]

results in the message

An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or assymetric keys.

Having a single member of, say, a Windows security group of SQL administrators be a database owner seems vulnerable to that account becoming disused. That seems to be what happened when the owning employee left the company in this question. This answer summarizes the problem of database ownership being an NT primary principal as follows:

Having the database ownership default to the NT primary principal creates a containment issue (the owner is an NT SID managed by AD and does not travel with the database files, the NT account can be thumbstoned etc etc etc).

I'm new to SQL Server administration and the restriction that only primary principals can be owners stands out compared with ownership of, for example, files. Per microsoft "How Owners are Assigned and Changed":

By default, a new object's owner is the security principal identified as the default owner in the access token attached to the creating process. ... The only exceptions occur when the user is a member of either the Administrators group or the Domain Admins group. In both cases, the Owner field in the user's access token contains the SID for the group, not the SID for the individual user account. The assumption is that administrative accounts are used only to administer the system and not for any individual purpose. As a result, objects created by one administrator can be managed by other administrators in the same group.

In other words,

• for files created by an administrator the default owner of the file is the group, while
• for databases the owner cannot be a group.

This leaves me with the following questions:

1. Is there an underlying reason why databases cannot be owned by a secondary principal?
2. Which principal should own a database? Is there a best practice? If so, what is the reasoning behind that best practice?

Answer 1

Honestly, sa or a disabled SQL Server account with absolutely minimum permissions is the best choice. As to why? Well, I could write out three or four paragraphs on that, or I can share my favorite article on What account should own the databases and why. A very thorough explanation covering all the bases. Highly recommended.

Here's a core excerpt: (Edited to add this 1/9/2020)

All right, well how about sa? It can already do anything it wants so it’s not like we are granting additional permissions, it can’t quit or be fired, and no one should be using it anyway. Heck, you probably have sa disabled, right? You know what? That’s actually perfectly reasonable! In fact sa is a really common id to use as the database owner.

The only risk is if the database becomes TRUSTWORTHY. Then you can create stored procedures that can act as sa. It’s honestly not a huge risk even then since you have to have impersonate permissions to create a procedure with the EXECUTE AS clause. Meaning that in order to create that stored procedure you have been explicitly granted the ability to impersonate a member of the db_owner role (please, please, please don’t do this) or be a member of db_owner yourself. That does mean that someone with db_owner can become a sysadmin but again, fairly minor risk. TRUSTWORTHY isn’t that common, and hopefully, if you are using it you understand the risks and are avoiding dangerous permissions.

But, a risk is a risk. So if we are feeling particularly paranoid and/or have a very sensitive system the easiest solution is to create a SQL login (with a stupidly complex password since no one is ever going to log in as it), disable it, and then use that as the owner.

Answer 2

1. Is there an underlying reason why databases cannot be owned by a secondary principal?

I'm not 100% certain of the reason for this restriction, though I suspect it has to do with the ability to impersonate (i.e. become) that SID. There are certain operations that can reach outside of the instance (e.g. to the OS, file system, etc) and the logic is usually to impersonate a Windows login, or use the SQL Server service account for SQL logins. A process cannot impersonate a group. However, I guess the logic could also use the SQL Server service account if the SID is a Windows Group, couldn't it? I dunno, can't think of any half-way reasonable explanation besides it simply being arbitrary.

I can say, though, that I disagree with the notion/advice that one shouldn't use Windows Logins as DB owners, at least for the stated reason. There isn't much difference, in terms of backing up and restoring a DB onto a different server, between SIDs created within SQL Server or coming from Windows or AD. In either case you can quite easily restore onto an instance that knows not of the DB owner's SID. It might be slightly easier to script out and transfer a login to recreate on the new instance such that it can match the SID of the dbo user in the database upon restore. But, the owner SID will initially be the SID that executed the RESTORE and something will need to be updated regardless (unless you use sa as that is guaranteed to be on the new instance, but that doesn't make it a good choice; more on that in a moment). In the end, it's rather easy to change the owner both in sys.databases as well as the dbo user in the DB itself, or to simply update the SID of the dbo user in the DB to an existing one on the new instance after doing the restore. There are several options, none being all that difficult.

And the concern about the SID not "traveling with the database files" should not apply to the physical MDF, NDF, LDF files as those should be owned by the SQL Server service account, not the database owner.

2. Which principal should own a database? Is there a best practice? If so, what is the reasoning behind that best practice?

The answer here comes down to priorities: Convenience xor Security? Pick one.

One issue is that the database owner is used to determine permissions for certain operations. The sa account, by its very nature, is not restricted from anything (outside of maybe placing restrictions on itself, but now we're just being silly). I'm not sure what that list of operations is, but using a low-privileged SQL Server login as the owner is definitely one way that people discover what those operations are ;-) (hence why so many people prefer to use sa, even if it is a poor choice; more on that in a moment).

That issue is separate from the privilege-escalation issue that can occur when executing code that has EXECUTE AS... in it. Whether it's executing as owner or dbo does not make a difference if the object is effectively owned by dbo (either directly, or by existing in a dbo-owned schema). And technically, that is also effectively the same as the proc being created with EXECUTE AS [bob] if [bob] is a login that is a member of the sysadmin fixed server role. In these cases, anyone executing the module (stored procedure, trigger, scalar UDF, table-valued function, etc) will then assume those instance-level permissions IF the database property of TRUSTWORTHY is enabled. The post that Laughing Vergil linked to mentions needing IMPERSONATE permission for the EXECUTE AS, but that misleading. The IMPERSONATE permissions is only needed for creating the module with the EXECUTE AS .... But, IMPERSONATE is not required when executing a module created with EXECUTE AS.... So, not a completely wide-open door, but anyone with EXEC permissions on a proc created with EXECUTE AS 'dbo' will be executing the code within that module as a sysadmin if the DB owner login is a member of sysadmin fixed server role, and TRUSTWORTHY was enabled in that DB.

Soooo:

• If you want the convenience of never having to think about stuff or possibly take a few minutes to set up proper security, then sa is as convenient as it gets.
• If you want proper security and can accept occasionally running into a permissions error which leads to some additional minor work (though not much) to set up Module Signing (and after the initial set up, even quicker / easier to add additional permissions as needed, if needed) then use a low-privileged SQL Server login.

Personally, I go with the second option (low-privileged SQL Server login) for Production, and for convenience in testing / demos I used sa or some other sysadmin-having login.