I have two databases, both owned by
sa. All objects in both databases are in schema
I have a user
dbSafe who needs to select from a view that joins tables from both
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.
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?