I have 2 databases in MS-SQL Server 2012. The first database, ‘DatabaseA’, is accessed by multiple databases including my second database ‘DatabaseB’. I have a view called ‘vwDB_B’ in ‘DatabaseB’ that joins/filters some information from both ‘DatabaseA’ and ‘DatabaseB’ (The view is set to run under an SQL-id that has access to both ‘DatabaseA’ and ‘DatabaseB’. Both DBs are on the same server. DatabaseB accesses objects in DatabaseA through their fully qualified names.
Now, we have an SQL-id that will be used by external users called ‘ExternalID’. This SQL-id should be able to retrieve data from ‘vwDB_B’. How can I achieve it?
If I give SELECT access to the ‘ExternalID’ on the ‘vwDB_B’ view, I get an error message that the ID doesn’t have the access to ‘DatabaseA’. I don’t want to grant read-only access to ‘ExternalID’ (or add it to a read-only role) on ‘DatabaseA’ because this DB has some sensitive information and I only want this id to access the information provided by my ‘vwDB_B’ view.
Best Answer
You can grant access to the view without granting any rights on the underlying tables using Ownership Chaining.
In this scenario you would need to enable Cross Database Ownership Chains, and grant the user access to the target database, but not SELECT on any of the tables.