patternsqlMinor
Why is database ownership by groups not allowed? Which user should own a database?
Viewed 0 times
whygroupsalloweduserdatabaseownshouldwhichnotownership
Problem
Per microsoft "ALTER AUTHORIZATION for databases for SQL Server":
Requirements for the new owner:
The new owner principal must be one of the following:
Apparently a group cannot own a database. Indeed invoking
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 an
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 an
Solution
Honestly,
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.
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.
Context
StackExchange Database Administrators Q#256938, answer score: 9
Revisions (0)
No revisions yet.