I have two databases, both owned by sa
. All objects in both databases are in schema dbo
.
I have a user bob
in dbSafe
who needs to select from a view that joins tables from both dbSafe
and dbRestricted
. I don't want to grant bob
direct permission to those tables in dbRestricted
, since they contain columns I don't want bob
to see.
I've granted bob
SELECT
permission on the view, and SET DB_CHAINING ON
for both databases, but I'm still getting an error:
The server principal "bob" is not able to access the database "dbRestricted" under the current security context.
Am I misunderstanding what cross-database chaining does?
If the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.
Can database chaining work in this scenario, with database owner sa
and schema dbo
? Or does it have to be an explicit login account/non-default schema?
The accepted answer to this question suggests creating a view in dbRestricted
and assigning direct permission to bob
, would that work in this case? What if adding a view to that database isn't an option for me (due to developer/vendor restrictions)? Isn't that what cross-database chaining is for?