In the context of another principal what permissions hinge on the identity of the database owner?
I had meant to write a blog post where I explain this in detail (more than is appropriate here), with example scripts, etc, but just haven't had the time. But, rather than have this remain unanswered until I have time to write that post (test scripts are done but need to be cleaned up), I will post the basic info here and come back later to adjust once that post is published.



There are two related scenarios that I know of that are dependent on the permissions of the database owner:


Trying to access instance-level resources, or another database, while using either impersonation or an App Role
Trying to obtain either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (instance-level permissions) while using unsigned SQLCLR assemblies (and using either SQL Server 2005 - 2016 or SQL Server >= 2017 and not using the horrible "trusted assemblies" hack)


Both scenarios are related by being times when enabling TRUSTWORTHY is required. And, both are also scenarios that can avoid the evil TRUSTWORTHY ON by implementing the far better approach of Module Signing.

Scenario #1

is very similar to, but still not the same as, actually accessing instance-level resources or another database under impersonation. There is a lot of overlap as they are two parts of the same functionality. I would guess that most of the time impersonation is being used to become the dbo user via the EXECUTE AS clause of a CREATE {module} statement, specifying either OWNER or N'dbo'. In that case it's very difficult to separate the indirect requirement, but impersonating a non-dbo user makes it clear.

When impersonating a user (we're assuming a non-dbo user), in order to access instance-level resources or another database, not only does the current database need to be marked as TRUSTWORTHY, but the login associated with the dbo user of the current database (i.e. same SID between the login and dbo) needs to have the AUTHENTICATE SERVER permission. This is not well known, which is likely why so many people simply resort to having sa own the database: it has all permissions. Many people, I'm sure, who already have their DBs owned by sa might never even know that this is a potential problem because the condition was already satisfied when they enabled TRUSTWORTHY in order to get the impersonation working.

EXAMPLE:

Setup:


There is a database, [TestDB], that has TRUSTWORTHY set to ON.
Database [TestDB] is owned by login O (this means that the dbo user in [TestDB] has the same SID as login O). O is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
The application connects as login A that has an associated user A in database [TestDB]. Login A is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
There is also a login, P, that has an associated user P in database [TestDB]. Login P is not a member of sysadmin nor has CONTROL SERVER, but does have the VIEW SERVER STATE permission.
There is a stored procedure in [TestDB] named dbo.ViewAllSessionInfo that selects from sys.dm_exec_sessions and sys.dm_exec_requests, and is not filtered to only display the row for the current login or SPID / session_id.


Behavior:


Login A (the app login) can execute dbo.ViewAllSessionInfo, but only gets 1 row returned, the row for the current session.
The stored procedure is altered to contain the EXECUTE AS N'P' clause (P has the required permission).
Login A executes dbo.ViewAllSessionInfo, but still only gets 1 row returned, the row for the current session (even though the stored procedure is now executing as P, and P does have VIEW SERVER STATE permission, and the database has TRUSTWORTHY enabled).
Login O is then granted the AUTHENTICATE SERVER permission.
Now, when login A executes dbo.ViewAllSessionInfo, rows for all sessions are returned (even though the only new permission added was granted to O, and not to either A or even to P).


Scenario #2

occurs when creating or altering an assembly, or even trying to use any code (stored procedure, trigger, functions, or type) from within an assembly that has been marked as either EXTERNAL_ACCESS or UNSAFE. Additionally, starting in SQL Server 2017, if you keep the "security" setting of "CLR strict security" enabled (the default), then even assemblies marked as SAFE need to meet this requirement (again, we are assuming that neither module signing nor "trusted assemblies" is being used). In this case, assuming that a non-dbo account is attempting one of those actions, the login associated with the dbo user of the current database (i.e. same SID between the login and dbo) needs to have either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. This is more widely known than Scenario #1 (due to an error message that provides this information), yet many people simply resort to having sa own the database (again, everything "magically" works). Many people, I'm sure, who already have their DBs owned by sa might never even know that this is a potential problem because the condition was already satisfied when they enabled TRUSTWORTHY in order to get the operation working.

EXAMPLE:

Setup:


There is a database, [TestDB], that has TRUSTWORTHY set to OFF (different than test case for Scenario #1).
Database [TestDB] is owned by login O (this means that the dbo user in [TestDB] has the same SID as login O). O is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
The application connects as login A that has an associated user A in database [TestDB]. Login A is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
There is also a SQLCLR assembly, C, in database [TestDB]. Assembly C has a PERMISSION_SET of UNSAFE.
There is a stored procedure in [TestDB] named dbo.DoUnsafeStuff that is really a .NET method in assembly C.


Behavior:


Login A (the app login) can execute dbo.DoUnsafeStuff, but gets a security error.
Database [TestDB] is altered to set TRUSTWORTHY to ON.
Login A can execute dbo.DoUnsafeStuff, but still gets a security error.
Login O is then granted the UNSAFE ASSEMBLY permission.
Now, when login A executes dbo.DoUnsafeStuff, the stored procedure operates as expected, without the security error (even though the only new permission added was granted to O, and not to A).


&nbsp;

In both scenarios,

the required permission is not required for the principal performing the action. It's more of a gate-keeper to determine if the requested action is allowed to be performed by any principal within that database.