Sql-server – In the context of another principal what permissions hinge on the identity of the database owner

access-controlpermissionssql serverwindows

By "database owner", here, I mean the principal whose SID is sys.databases.owner_sid.

This answer to the question of best practices for database ownership includes the following:

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).

This suggests that the permissions granted to the database owner are factored into permissions checks during operations invoked in the security context of other principals. This sounds reasonable and I can imagine a circumstance where changing the database ownership resolved some permissions issue, but I have not seen that play out first-hand.

This answer seems to support this suggestion:

the most important thing: the database owner has important side effects, specifically the EXECUTE AS context. This later problem is what burns most users. Since Service Broker makes extensive use of EXECUTE AS (the message delivery has an implicit EXECUTE AS context, as well as queue activation that has an explicit one) is usually Service Broker users that discover this problem first.

This suggests that EXECUTE AS context could lead to unintended escalation of privileges to those of the database owner.

Microsoft's "Summary of the Permission Check Algorithm" does not, however, seem to mentioned the database owner:

Security context

This is the group of principals that contribute permissions to the access check. These are permissions that are related to the current login or user, unless the security context was changed to another login or user by using the EXECUTE AS statement. The security context includes the following principals:

  • The login
  • The user
  • Role memberships
  • Windows group memberships
  • If module signing is being used, any login or user account for the certificate used to sign the module that the user is currently executing, and the associated role memberships of that principal.

Microsoft's Chart of SQL Server Permissions also doesn't seem to mention owner_sid (although a lot seems to hinge on the db_owner role, but that is a different matter).

Furthermore, Microsoft's "Understanding Execution Context" does not seem to make any special mention of the database owner.

This raises the following questions:

  1. What permissions, if any, hinge on sys.databases.owner_sid?
  2. Are there operations that SQL Server invokes using sys.databases.owner_sid to which the "SQL Server Permission Check Algorithm" does not apply?
  3. Does EXECUTE AS ... context somehow allow a normal user to inherit the permissions of sys.databases.owner_sid?

Best Answer

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:

  1. Trying to access instance-level resources, or another database, while using either impersonation or an App Role

  2. 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:

  1. Login A (the app login) can execute dbo.ViewAllSessionInfo, but only gets 1 row returned, the row for the current session.
  2. The stored procedure is altered to contain the EXECUTE AS N'P' clause (P has the required permission).
  3. 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).
  4. Login O is then granted the AUTHENTICATE SERVER permission.
  5. 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:

  1. Login A (the app login) can execute dbo.DoUnsafeStuff, but gets a security error.
  2. Database [TestDB] is altered to set TRUSTWORTHY to ON.
  3. Login A can execute dbo.DoUnsafeStuff, but still gets a security error.
  4. Login O is then granted the UNSAFE ASSEMBLY permission.
  5. 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).

 

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.

Related Question