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:
- Is there an underlying reason why databases cannot be owned by a secondary principal?
- Which principal should own a database? Is there a best practice? If so, what is the reasoning behind that best practice?