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.