1

This is my scenario:

  • Windows User Gabe has db_owner access of database DB1.
  • Windows User Gabe has db_reader on database DB2.
  • When the user executes UPDATE DB2.dbo.Tbl1 SET X = 1, they get "The UPDATE permission was denied.." as expected.
  • when user executes a proc DB1.dbo.uspUpdateTable containing the same UPDATE statement above, it succeeds.

I thought this might be due to database chaining or trustworthy settings but they are not enabled.

Is this the expected behavior for db_owner privilege?? And is there a way to prevent this?

SQL Server 2017

3

Below are cases where updates will be allowed via the stored stored procedure without the caller having update permissions on the table in the other database.

Ownership chaining

The databases involved have the DB_CHAINING option set or the 'cross database ownership chaining' option turned on at the server level. In the case of dbo-owned objects, the databases must also the same owner (i.e. dbo user maps to same login) to mainain an unbroken ownership chain.

EXECUTE AS

The stored procedure includes an EXECUTE AS USER specification and the user (mapped to same login) exists in the other database with update permissions. Note that the database must be TRUSTWORTHY in order for the EXECUTE AS context to be honored by other databases so this does not apply in your case.

Module Signing

The stored procedure is signed by a certificate and a user created from that certificate exists in the other database with permissions to update the table.

I see from your comment that 'cross database ownership chaining' was the culprit. This also implies the databases also have the same owner. I suggest you change the database owner for databases where non-sysadmin role members can create dbo-owned objects to mitigate security risk.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.