How to allow an SQL-ID to access one database through a view in another?

Question

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.

Answer 1

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.

• Vicky1729: Enabling Cross DB Ownership Chains did help but in itself was not sufficient. Even after further research, I still couldn't get the view working. So I changed the View into a Stored Procedure and had to use the Impersonation to get this working. Not an ideal solution but we were ultimately fine with it. Got help from here, here and here. Thanks David.