Consider a scenario where you have a view object in a database named "Database A" tries to access a table from "Database A" as well as a table from "Database B". In this scenario to run the view the user must also be a user in Database B along with Database A. If these databases are tightly linked, cross database ownership chaining can be used where user can access the table from Database B only through the view from Database A but doesn't have explicit permissions to query the table in Database B.
Steps to enable Cross Database Ownership Chaining
Set both Database A and Database B in the chaining as trustworthy.
ALTER DATABASE Database1 SET TRUSTWORTHY ON;
ALTER DATABASE Database2 SET TRUSTWORTHY ON;
Make sure that both Tables inside the Database A and Database B belong to the same owner.
Enable Cross Database Ownership Chaining. This can be done on whole instance level or specific databases.
For instance level
Exec sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;
For database level
ALTER DATABASE Database1 SET DB_CHAINING ON;
ALTER DATABASE Database2 SET DB_CHAINING ON;
No comments:
Post a Comment